Did I Break the MONTH Function with the TEXT Function?

[preitano]

Hi,

I'm calculating the first Thursday of the next month based on today's date, which in the screenshot below properly generates 2/1/24.
Next, I'm attempting to get the 3 letter abbreviation of the month name for a search, so I'm using MONTH combined with TEXT.

MONTH on its own generates the expected 2, but when I couple it with TEXT I'm getting "Jan" for some reason.

I've displayed the formulas next to the results.

What am I doing wrong?

Thanks

 

[RoryA]

You don't need (or want) the MONTH function there at all. Just use

=TEXT($J$9,"mmm")

 

[Joe4]

Just an explanation what is going on...

It is important to understand how Excel stores dates. It stores them as numbers, specifically the numbers of days since 1/0/1900 (enter any valid date, change the format of the cell to General, and you will see the date as Excel sees it). So then dates are really just large integers with a special date format.

So, if you use:

MONTH(J9)

that will return a value of "2".
If you try to format "2" as a date, you are actually referring to the date 1/2/1900, which is why your TEXT function on that calculation returns January.

 

[preitano]

Thanks!