Calc one month prior - text only

  • Thread starter Thread starter Matt Thomas
  • Start date Start date
=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]...
 
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.
 
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.
 
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...".
 
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]...
 
Back
Top