Trouble adding or subtracting from the current month

[Dave L]

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?

 

[T. Valko]

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

 

[David Biddulph]

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.

 

[daddylonglegs]

Try this

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

 

[daddylonglegs]

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

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/

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?

 

[Dave L]

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!

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

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/

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?

 

[Tyro]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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

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

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/

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?

 

[David Biddulph]

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

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

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

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?