Extract month from a date in a cell

[excelblob]

Well, this shouldn't be that hard but it is for me..

I have a date as 1/1/11 format in a date-formatted cell A1 for example. I want to extract the month in MM format, i.e. January, or "1" is extracted as "01", etc and just place it into A2.

So far, I've found an option to use format function in VBA and "MM", but I'm not familiar with VBA yet.

Of course, left function does not seem to be helping, at least the way I'm using it.

Any ideas? Thanks.

 

[Norie]

Try the MONTH worksheet function to get the month and use the format 00 to get the leading zero.

 

[excelblob]

The month function is easy for me to understand. Thanks. But use format to get 00? Is this a function name I type somewhere? Not quite sure.

 

[Norie]

In Excel 2007/2010 on the ribbon there's a section called Number.

If you click the tiny arrow in the bottom right of that you'll open up the Format dialog.

Pick Custom from the list on the left and type 00 in the box labelled Type.

Press OK

If it's an earlier version goto Format>Cells... and you'll get the same dialog.

Alternatively you can use this:

=TEXT(MONTH(A1), "00")

Where the date is in A1.

 

[excelblob]

Thanks Norie! This does exactly what I needed.

 

[ganeshpoojary05]

You can also try:

=TEXT(A1,"MM")

 

[Norie]

Actually, if you are going to use a formula don't use the one I posted.

It's wrong.