Getting month number from month name

[oliviatww]

Hi there,

I know how to get month name from month number, but not sure how to get month number from month name.

For example,

Aug -> 08

Jan -> 01

Dec -> 12

Any help is appreciated.

Thanks a lot.

[jagan]

Hi,

Try like this

=Num(Match(MonthDimensionName, 'Jan', 'Feb', 'Mar', 'Apr', 'May'................................), '00')

OR

=Date(Date#(MonthDimensionName, 'MMM'), 'MM')

Regards,

Jagan.

[oliviatww]

Hi Jagan,

Thanks for your reply.

I tried the second example you gave, the "=Date(Date#(MonthDimensionName, 'MMM'), 'MM')" and it works fine if MonthDimensionName is any number and that it is not used/ apply in another function.

For example, I have a field call PostDate where the format of that field is '10AUG2014:00:00:00', what I would like to do is to convert them into a date like '10-08-2014' or any format that is related to date. So basically the formulae you gave don't work when I apply that in MakeDate() function.

E.g. =MakeDate( Mid(PostDate, 6, 4), Date(Date#(Mid(PostDate, 3, 3), 'MMM'), 'MM'), Left(PostDate, 2)) returns -

And it work if

E.g. =MakeDate( Mid(PostDate, 6, 4), 10, Left(PostDate, 2)) return 10-08-2014

(Assuming '10AUG2014:00:00:00' is selected in the PostDate listbox)

Thanks

[jagan · Accepted solution]

Hi,

Initially you mentioned this

For example,

Aug -> 08

Jan -> 01

Dec -> 12

So, I given that script.

Now try this script

=Date(MakeDate( Num(Mid('10AUG2014:00:00:00', 6, 4)), Month(Date#(Capitalize(Mid('10AUG2014:00:00:00', 3, 3)), 'MMM')), Num(Left('10AUG2014:00:00:00', 2))), 'DD-MM-YYYY')

OR

=Date(MakeDate( Num(Mid('10AUG2014:00:00:00', 6, 4)), Month(Date#(Mid('10AUG2014:00:00:00', 3, 3), 'MMMM')), Num(Left('10AUG2014:00:00:00', 2))), 'DD-MM-YYYY')

Hope this helps you.

Regards,

Jagan.

[oliviatww]

Hi Jagan,

Truly sorry about that because I thought giving a simple example can make less confuse to others.

Anyway your script works!!!

Thanks a lot for that.