How can I extract a month from Excel date?

[Guest]

I have a cell, U9 showing the date, custom formatted as dd-mmm-yy. e.g.
31-Oct-06
I need another cell to show just the month. e.g. Oct
The formula =MID(U9,4,3) always returns a 2 digit number regardless of what
date is in U9. I assume it is the Excel date value that the MID formula is
taking the final two digits from.
How do I get it to show the month as text. e.g. Oct

Thanks,
Lee

 

[Niek Otten]

Hi Lee,

=U9, Format Custom as ddd

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a cell, U9 showing the date, custom formatted as dd-mmm-yy. e.g.
| 31-Oct-06
| I need another cell to show just the month. e.g. Oct
| The formula =MID(U9,4,3) always returns a 2 digit number regardless of what
| date is in U9. I assume it is the Excel date value that the MID formula is
| taking the final two digits from.
| How do I get it to show the month as text. e.g. Oct
|
| Thanks,
| Lee
|

 

[Guest]

Try: =TEXT(U9,"mmm")

 

[Niek Otten]

<ddd>

make that mmm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Lee,
|
| =U9, Format Custom as ddd
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||I have a cell, U9 showing the date, custom formatted as dd-mmm-yy. e.g.
|| 31-Oct-06
|| I need another cell to show just the month. e.g. Oct
|| The formula =MID(U9,4,3) always returns a 2 digit number regardless of what
|| date is in U9. I assume it is the Excel date value that the MID formula is
|| taking the final two digits from.
|| How do I get it to show the month as text. e.g. Oct
||
|| Thanks,
|| Lee
||
|
|

 

[Guest]

Thanks for both suggestions. Both do the job.

 

[Max]

You're welcome, Lee !

 

[Max]

.. Both do the job.

In terms of showing / display in the formula cell, yes. But there's a subtle
difference in the underlying values. Formatting retains it as a number,
while using TEXT converts it to text. The underlying value would of course
impact any downstream formulas pointing to that formula cell.

---