Get previous month number

sharky12345

Well-known Member
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?

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

What's in U2?

Andrew Poulsom

MrExcel MVP
Maybe:

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

sharky12345

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

Andrew Poulsom

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

sharky12345

Well-known Member
Sorry, typo - I'm getting '#NAME?'

Andrew Poulsom

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

sharky12345

Well-known Member
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

MrExcel MVP
This formula should work in XL2003 and above...

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

sharky12345

Well-known Member
Perfect, thanks Rick, and thank you to Andrew for his perseverance!

Replies
4
Views
521
Replies
12
Views
619
Replies
3
Views
290
Replies
1
Views
215
Replies
5
Views
212

1,195,949
Messages
6,012,481
Members
441,701
Latest member
vnkendijs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back