Calc one month prior - text only

[Matt Thomas]

I'm trying to calculate the prior month (in 3 letter format) using a formula where I populate the cell reference with the current month.

i.e. I enter Nov in A1 and A2 has a formula to give me Oct

Any ideas?


Submitted via EggHeadCafe - Software Developer Portal of Choice
Select Subset of Rows in ADO.NET DataTable
http://www.eggheadcafe.com/tutorial...15-185933676c4f/select-subset-of-rows-in.aspx

 

[Harald Staff]

=TEXT(DATE(2000,MONTH(DATEVALUE(A1&" 1"))-1,1),"mmm")

the A1&" 1" part is highly vulnerable to regional date settings, change to
whatever natural format your computer will accept nov 1 as a date.

in message
news:[email protected]...

 

[ZootRot]

You need to take into consideration your month being January and, therefore,
the month-1 being in year-1.

Assuming your date is in cell A1:

=IF(MONTH(A1)=1,DATE(YEAR(A1)-1,12,1),DATE(YEAR(A1),MONTH(A1),1)-1)
Format your calculation cell in whatever format you want to see the date.

If you really need it to be text, just convert it with the text function.

 

[Rick Rothstein]

Here is a nice short formula for you to use...

=TEXT((1&A1)-1,"mmm")

--
Rick (MVP - Excel)


in message
news:[email protected]...

 

[ZootRot]

That's an ingeniously simple solution, Rick.

Just one point of note, however: it only works if the text month is only
text and not just formatted as the month name.

 

[Rick Rothstein]

Correct... it only works if the month's abbreviated or full name is text,
but I assumed that is what the OP meant he had when he said "I enter Nov in
A1...".

 

[Rick Rothstein]

My first post gave you a solution if the month name's abbreviation (it works
for the full name too) is typed in, which is what I assumed you meant by "I
enter Nov in A1"; however, if you really do have a date in A1 and it is
formatted to only show the month abbreviation, then you can use this nice
short formula for that case...

=TEXT(A1-DAY(A1),"mmm")

--
Rick (MVP - Excel)


in message
news:[email protected]...

 

[Harald Staff]

doh! of course. neat