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

[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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

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