excel-formulaexcel-dates

Formula returning previous month in `mmmm` format, only returns January

I have a worksheet that I use every month and would like a formula that automatically updates to the previous month.

=TEXT(MONTH(TODAY())-1,"mmmm")

always returns January.

However, just MONTH(TODAY())-1 correctly returns 11.

Why then when I format with TEXT() does it change to January?

Solution

  • As you have found MONTH(TODAY())-1 returns 11. The 11 is taken as the number of days from 1/1/1900 when used as you are in the TEXT(), which is the 11th of January 1900 so the month is January.

    Use:

    =TEXT(EOMONTH(TODAY(),-1),"mmmm")