I'm trying to write a formula that will take the current month and subtract 3
from it and then display the full month name. This formula returns a 7 as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into another
formula. How to i make it return the correct name of the month?

That's happening because the result of:

MONTH(NOW())-3

= 7

In the TEXT function, the 7 is being evaluated as serial date 7 or January 7
1900.

Try this:

=TEXT(30*(MONTH(NOW())-3),"mmmm")

--
Biff
Microsoft Excel MVP


"Dave L" wrote in message
...
I'm trying to write a formula that will take the current month and
subtract 3
from it and then display the full month name. This formula returns a 7 as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into
another
formula. How to i make it return the correct name of the month?

The reason why you are seeing "January" is that you have fed the TEXT
function the number 7 as an input, but it wants a date as an input. Excel
dates count from January 1900. Number 7 repressents 7th January 1900.

If you want to feed TEXT with a date 3 months ago, try
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),"mmmm")
[but remember the usual questions, such as what date do you regard as being
3 months before 31st May?]
Note that I've used TODAY(), as that gives a date. NOW(), which you used,
gives date and time, but the time is unnecessary in this context.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/
"Dave L" wrote in message
...
I'm trying to write a formula that will take the current month and
subtract 3
from it and then display the full month name. This formula returns a 7 as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into
another
formula. How to i make it return the correct name of the month?

Try this

=TEXT(MOD(MONTH(TODAY())-3,12)*30,"mmmm")

"Dave L" wrote:

I'm trying to write a formula that will take the current month and subtract 3
from it and then display the full month name. This formula returns a 7 as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into another
formula. How to i make it return the correct name of the month?

Hello David,

That won't give the correct result in all circumstances, e.g. on 30th and
31st May it'll return "March".

Biff's formula above will give an error in January or February....

....and my suggestion above is also flawed, this should fix it....


=TEXT((MOD(MONTH(TODAY())-4,12)+1)*30,"mmmm")

"David Biddulph" wrote:

The reason why you are seeing "January" is that you have fed the TEXT
function the number 7 as an input, but it wants a date as an input. Excel
dates count from January 1900. Number 7 repressents 7th January 1900.

If you want to feed TEXT with a date 3 months ago, try
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),"mmmm")
[but remember the usual questions, such as what date do you regard as being
3 months before 31st May?]
Note that I've used TODAY(), as that gives a date. NOW(), which you used,
gives date and time, but the time is unnecessary in this context.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/
"Dave L" wrote in message
...
I'm trying to write a formula that will take the current month and
subtract 3
from it and then display the full month name. This formula returns a 7 as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into
another
formula. How to i make it return the correct name of the month?

Thanks guys. Daddylonleg's formulas took care of when i needed to subtract
days from the current month, but bombed out if i used it to add to the
current month once i switch my clock over to January. T Valko's formula took
care of that though so i used them both. Thanks again!

"daddylonglegs" wrote:

Hello David,

That won't give the correct result in all circumstances, e.g. on 30th and
31st May it'll return "March".

Biff's formula above will give an error in January or February....

...and my suggestion above is also flawed, this should fix it....


=TEXT((MOD(MONTH(TODAY())-4,12)+1)*30,"mmmm")

"David Biddulph" wrote:

The reason why you are seeing "January" is that you have fed the TEXT
function the number 7 as an input, but it wants a date as an input. Excel
dates count from January 1900. Number 7 repressents 7th January 1900.

If you want to feed TEXT with a date 3 months ago, try
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),"mmmm")
[but remember the usual questions, such as what date do you regard as being
3 months before 31st May?]
Note that I've used TODAY(), as that gives a date. NOW(), which you used,
gives date and time, but the time is unnecessary in this context.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/
"Dave L" wrote in message
...
I'm trying to write a formula that will take the current month and
subtract 3
from it and then display the full month name. This formula returns a 7 as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into
another
formula. How to i make it return the correct name of the month?

You can use the edate function if you're using Excel 2007 or if in a prior
version you have the Analysis Toolpack installed

=TEXT(EDATE(TODAY(),-3),"mmmm")

Tyro

"Dave L" wrote in message
...
I'm trying to write a formula that will take the current month and
subtract 3
from it and then display the full month name. This formula returns a 7 as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into
another
formula. How to i make it return the correct name of the month?

That won't give the correct result in all circumstances, e.g. on 30th and
31st May it'll return "March".
7th January 1900.

If you want to feed TEXT with a date 3 months ago, try
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),"mmmm")
[but remember the usual questions, such as what date do you regard as
being
3 months before 31st May?]

I'm not sure why my formula works when David's doesn't (for the dates you
mentioned), but I figured the actual date is immaterial for getting the
month, so I prefilled the year and day in and got this...

=TEXT(DATE(2000,MONTH(A1)-3,1),"mmmm")

which returns February for the dates you mentioned (the date being in A1 for
my example formula). So now the question is.... why does it work when
David's doesn't?

Rick

That won't give the correct result in all circumstances, e.g. on 30th and
31st May it'll return "March".
7th January 1900.

If you want to feed TEXT with a date 3 months ago, try
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),"mmmm")
[but remember the usual questions, such as what date do you regard as
being
3 months before 31st May?]

I'm not sure why my formula works when David's doesn't (for the dates you
mentioned), but I figured the actual date is immaterial for getting the
month, so I prefilled the year and day in and got this...

=TEXT(DATE(2000,MONTH(A1)-3,1),"mmmm")

which returns February for the dates you mentioned (the date being in A1
for my example formula). So now the question is.... why does it work when
David's doesn't?

Never mind... I know why mine works... I use the first of the month for the
calculation thus avoiding end-of-month issues for months with differing
number of days. Again, since the month is all we are after, the year and day
are immaterial, so using any year and day one should always work (and the
formula is rather tight to boot).

Biff's formula above will give an error in January or February....

Ooops! Good catch. Looks like I wasn't paying attention to what I was doing.


--
Biff
Microsoft Excel MVP


"daddylonglegs" wrote in message
...
Hello David,

That won't give the correct result in all circumstances, e.g. on 30th and
31st May it'll return "March".

Biff's formula above will give an error in January or February....

...and my suggestion above is also flawed, this should fix it....


=TEXT((MOD(MONTH(TODAY())-4,12)+1)*30,"mmmm")

"David Biddulph" wrote:

The reason why you are seeing "January" is that you have fed the TEXT
function the number 7 as an input, but it wants a date as an input.
Excel
dates count from January 1900. Number 7 repressents 7th January 1900.

If you want to feed TEXT with a date 3 months ago, try
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),"mmmm")
[but remember the usual questions, such as what date do you regard as
being
3 months before 31st May?]
Note that I've used TODAY(), as that gives a date. NOW(), which you
used,
gives date and time, but the time is unnecessary in this context.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/
"Dave L" wrote in message
...
I'm trying to write a formula that will take the current month and
subtract 3
from it and then display the full month name. This formula returns a 7
as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into
another
formula. How to i make it return the correct name of the month?

You may have missed the part of my message below where I said:
"[but remember the usual questions, such as what date do you regard as being
3 months before 31st May?]"
--
David Biddulph

"daddylonglegs" wrote in message
...
Hello David,

That won't give the correct result in all circumstances, e.g. on 30th and
31st May it'll return "March".

Biff's formula above will give an error in January or February....

...and my suggestion above is also flawed, this should fix it....

=TEXT((MOD(MONTH(TODAY())-4,12)+1)*30,"mmmm")

"David Biddulph" wrote:

The reason why you are seeing "January" is that you have fed the TEXT
function the number 7 as an input, but it wants a date as an input.
Excel
dates count from January 1900. Number 7 repressents 7th January 1900.

If you want to feed TEXT with a date 3 months ago, try
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),"mmmm")
[but remember the usual questions, such as what date do you regard as
being
3 months before 31st May?]
Note that I've used TODAY(), as that gives a date. NOW(), which you
used,
gives date and time, but the time is unnecessary in this context.
--
David Biddulph

"Dave L" wrote in message
...
I'm trying to write a formula that will take the current month and
subtract 3
from it and then display the full month name. This formula returns a 7
as
expected:
=MONTH(NOW())-3

But when I add the Text function to it so it returns "July" it always
displays "January" no matter what number i subtract:
=TEXT(MONTH(NOW())-3,"mmmm")

I can't just format the cell because this needs to be embedded into
another
formula. How to i make it return the correct name of the month?