Calc one month prior - text only

H

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]...
 
Z

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.
 
Z

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.
 
R

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...".
 
R

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]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top