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

A

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
 
B

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)
 
F

Frank Kabel

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

if you want a string as return.
 
N

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi Frank!

See my reply to Bob.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

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

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

Norman said:
Hi Frank!

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

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

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)
 
B

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)
 
F

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")
 
F

Frank Kabel

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

Norman Harker

Hi Bob!

I'm inclined to agree about obtuseness of formula especially as a
corrected form is:

=TEXT(((MONTH(A1)-1)+12*(MONTH(A1)=1))*29,"mmm")

It has an advantage of 3 function calls v. 6 but that is hardly ever
an advantage unless used many times.

I think that MONTH(A1)*29 does have its place in our armoury but only
where we are converting a simple month number to a name and we it's
easier and a lot shorter than the alternatives.

But in this case OP now tells us he wants the year as well and that
makes use of the MONTH(A1)*29 approach much more complicated by having
to use:

=TEXT(((MONTH(A1)-1)+12*(MONTH(A1)=1))*29,"mmm")&"-"&YEAR(A1)-(MONTH(A1)=1)

The conclusion must be that these are best:

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

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

Substituting TODAY() for A1 if we want TODAY() within the formula.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Bob Phillips

Hi Norman,

I agree with most if not all you say ( as ever<vbg>). I found your formulae
interesting and will certainly keep them up my sleeve for some future use,
but as you say, the performance of worksheet formulae is rarely an issue,
and when it does become so is usually better served by a re-design. So I
will keep to my simplistic formulae in most cases.

As to the OP, how many times do we get provide a solution only to be told
that we had been given the full requirement. Good job we are saints isn't
it<ebg>

--

HTH

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