Get previous month number

[sharky12345]

I'm using this to get the Month number;

=MONTH(DATEVALUE(U2&" 1"))

Can anyone show me how to get the number of the previous month, so that if the Month is January it would show 12?

 

[Andrew Poulsom]

What's in U2?

 

[Andrew Poulsom]

Maybe:

=MONTH(EDATE(DATEVALUE(U2&" 1"),-1))

 

[sharky12345]

Andrew, U2 contains the month name only from a Data Validation list - your suggestion produces '#VALUE!'

 

[Andrew Poulsom]

My formula can't return #VALUE! unless yours does.

 

[sharky12345]

Sorry, typo - I'm getting '#NAME?'

 

[Andrew Poulsom]

What version of Excel do you have? Prior to Excel 2007 EDATE was in the Analysis ToolPak Add-In, which must be installed.

 

[sharky12345]

Ah, sorry - I normally use 2007 but I'm doing a project for someone who is using 2003 so developing it in that version.

 

[Rick Rothstein]

This formula should work in XL2003 and above...

=MONTH((U2&1)-DAY(U2&1))

 

[sharky12345]

Perfect, thanks Rick, and thank you to Andrew for his perseverance!