=text(now(),"Mmm")-1 ?

[Adam]

Morning All,

What I want is a formula that will give me last months
name, not this months!

the formula =TEXT(NOW(),"Mmm") provides this month but how
do I provide last months name in a Mmmm format? i.e
February.

Many Thanks,

Adam

 

[Bob Phillips]

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()),"mmm")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try
=TEXT(DATE(YEAR(NOW()),MONTH(NOW())-1,DAY(NOW())),"MMMM")

if you want a string as return.

 

[Norman Harker]

Hi Adam!

Either:
=(MONTH(TODAY())-1)*29
Format mmmm

Or:
=TEXT(MONTH(TODAY())-1)*29,"mmmm")

Both rely on the fact that the month numbers 1-12 multiplied by 29
produce date serial numbers that are in successive months of 1900.

There are formulas that rely on DATE but the shorter ones will fail
when the date is the 31st of a month and the month before does not
have a Day number 31.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob Phillips]

Hi Norman,

I think you mean

=TEXT((MONTH(TODAY())-1)*29,"mmmm")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi Bob!

This will fail on 31-May and any other month where the day number is
greater than the number of days in the previous month.

=TEXT(DATE(YEAR("31-May-2004"),MONTH("31-May-2004")-1,DAY("31-May-2004")),"mmm")
Returns May

You can use the Chip Pearson based formula:

=TEXT(DATE(YEAR("31-May-2004"),MONTH("31-May-2004")-1,MIN(DAY("31-May-2004"),DAY(DATE(YEAR("31-May-2004"),MONTH("31-May-2004"),0)))),"mmm")
Returns Apr

However, I found a much shorter way recently based upon a
idiosyncratic use of date serial numbers for 1900:

=TEXT((MONTH("31-May-2004")-1)*29,"mmm")
Or
=(MONTH("31-May-2004")-1)*29
Format mmm




--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Frank!

See my reply to Bob.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Norman
to prevent this the OP may use
=TEXT(DATE(YEAR(NOW()),MONTH(NOW())-1,1,"MMMM")

 

[Norman Harker]

Hi Bob!

Doh! Wrapping the answer in the TEXT function got me!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Andy B]

Hi

Another option would be
=TEXT(TODAY()-DAY(TODAY()),"MMM")

Andy.

 

[Guest]

Hi, that works great but... I also want the year so if this was to go back to January it would give me December 04 when it should be December 03

Any ideas

Many THank

Adam

 

[Norman Harker]

Hi Frank!

You caught a "Doh!" from me (see below). Missing parenthesis strikes
again!

=TEXT(DATE(YEAR(NOW()),MONTH(NOW())-1,1),"MMMM")

A good way round the month length problem.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

§$%$§
got me

 

[Norman Harker]

Hi Adam!

Now you tell us! <vbg>

That gives you a choice of three:

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,MIN(DAY(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),0))))
Format mmm-yyyy

Or:
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"mmm-yyyy")

Or:
=TEXT(TODAY()-DAY(TODAY()),"mmm-yyyy")


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Frank!

I think it's the affect of that gift of a 1000 camels on their way to
you.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Frank!

I think it's the affect of that gift of a 1000 camels on their way to
you.

lol
probably. Still willing to trade one of these camels with some beer
Frank

 

[Bob Phillips]

Hi Norman,

You are right, but surely this much simpler format suffices

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1,"mmm")

As for your variation, yes clever, but obtuse, and I personally try to avoid
such things as it can lead to problems later when someone else may try to
update the spreadsheet.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

or even

=TEXT(DATE(YEAR(NOW()),MONTH(NOW()),0,"mmmm")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi bob
I think today is the day of missing parenthesis
you probably meant
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"mmm")

 

[Frank Kabel]

Hi Bob
this is fun, spotting missing brackets <vbg>
=TEXT(DATE(YEAR(NOW()),MONTH(NOW()),0),"mmmm")