Will the formula below change to November automatically next month?

Yes it should, although I'm not sure why you're using '2004' in the formula.

You should be able to replace that whole formula with

=TEXT(TODAY(),"mmm")

Thanks to both. I was told that I have to use that formula because I'm using the same slightly edited formula to say the previous month like this...


and they said that using a simpler formula like wouldn't work properly because when January rolls around, it won't show December.

Any thoughts? Is this true?

Thanks

The logical test for the IF formula is not logical at all, it will be TRUE for all cases:

MONTH(TODAY())>0

Hence its uneccessary and Jonmo1's formula should suffice.

This will give you the name of previous month:

=TEXT(EOMONTH(B1,-1),"mmm")

Let's break down the formula...

=TEXT(DATE(2004,IF(MONTH(TODAY())>0,MONTH(TODAY()),12),1),"MMM")

MONTH(TODAY()) can only return a number from 1 to 12, it CANNOT return 0 or 13+

Therefor MONTH(TODAY()) will ALWAYS be a number greater than 0.
So
MONTH(TODAY())>0 will ALWAYS be true, it will NEVER Be false.

So, the date function will ALWAYS use
MONTH(TODAY()) as the month argument.
It will never use 12 (except when it is currently actually within the month of December)
But in that case, it's still because of Month(Today())>0 being True.
Not because of the 12 hard coded into the False part of the IF.

So the formula will ALWAYS return the Current Month in text format.
As will
=TEXT(TODAY(),"mmm")

The Year is completely irrelevant, because you're using 1 as the DAY argument in the Date function.
And The formula is only interested in the MONTH, not the actual complete date.
The only way the year would ever matter is in a Non Leap year, and you were using 29+ as the DAY argument.


There is no cercumstance in which these 2 formulas would produce different results.
=TEXT(DATE(2004,IF(MONTH(TODAY())>0,MONTH(TODAY()),12),1),"MMM")
=TEXT(TODAY(),"mmm")


By the way, who is 'They' ?

VBAProIWish said:

I was told that I have to use that formula because I'm using the same slightly edited formula to say the previous month like this...

Click to expand...

The formula you posted will not return the 'previous' month.

If the goal is to return the previous month, try

=TEXT(DATE(2004,MONTH(TODAY()),0),"MMM")

omg, I have spent the last 20 minutes trying to find that conversation about the previous date issue and why it had to be like that. I totally believe you Jonmo, but I really want you to see that discussion on here. I searched all my posts and cannot find the thread . Feel free to search on "TEXT(DATE(2004,IF("

There will be many posts containing that text, I just don't know where I found it. Thank you for the great explanation though!

P.S. If you decide to search for the thread and find it, please let me know.

Thanks much!

To be clear, I'm not trying to say your formula is 'wrong'.
It certainly does give the desired result of the current month in text format.

I'm just saying that it's just unnecessarily over complicated.
Just trying to help by showing that the same result can be achieved with a much simpler formula.

Also, I'm pretty sure your actual question was answered with the very first reply by madaknarf
Yes, the formula will change to Nov come November first.

So I think I'll pass on the research...

lol, allllll good. I actually got that code from somewhere on this forum and don't want to take credit for it.

Maybe the original person that gave me the code, gave it to me because I wanted it in code format, but I'm sure your code works that way also.

Thanks so much for your input!