Have a Month Drop down return a number value

[jak82]

Hi,

I have a drop down list defined in a sheet called 'List Box' which lists all the months Jan-Dec.

I am referencing it in the main sheet as part of cell. I am wanting to return a number for the value selected for example 'March' returns a value of 03 when referenced in a function.

This is the code I am currently using as a function

=-GETPIVOTDATA("journal_amount",'Pivot Table'!$A$3,"nlyear", 'P&L'!C$5&"","trans_period",'P&L'!C$4&"","account_code",'P&L'!$C10&"")

So I am wanting 'P&L'!C$5&"" to pull the March value as 03

Many Thanks

 

[Jonmo1]

You're welcome..

=TEXT(1&'P&L'!C$5,"mm")

If C5 = August or Aug
Then 1&C5 = 1August or 1Aug

So it's just representing the First of August, do make a Date String.

So then you have
=TEXT("1Aug","mm")
Then the Text function can consider it a Date, and return the month element of that date in the format of mm

If you do
=TEXT("Aug","mm")
It's not going to work, because AUG is just a text string, it's not a date. It doesn't have at least 2 elements of a date.
Adding the 1, makes it look more like a date, it has at least 2 elements of a date.