Month number to Text formula

**JyothiGrace** · 10-19-2021, 10:25 AM

I am racking my brains but can't figure out why the formula is not working for Aug thru Nov. I used Text(A2, "mmm"). Any ideas?

**Pepe Le Mokko** · 10-19-2021, 10:30 AM

Try =DATE(YEAR(TODAY()),Table1[@[Data.DOB (Month)]],1) pulled down and formatted as "mmm" or insert it in the TEXT function

**AliGW** · 10-19-2021, 10:31 AM

I hope this will explain why:

AliGW on MS365 Insider (Windows) 32 bit

	A	C	D	E	F
1	Data.DOB (Month)
2	7	Jan	07/01/1900	Jul	=TEXT(DATE(2021,Table1[@[Data.DOB (Month)]],1),"mmm")
3	8	Jan	08/01/1900	Aug
4	9	Jan	09/01/1900	Sep
5	10	Jan	10/01/1900	Oct
6	11	Jan	11/01/1900	Nov

Sheet: Sheet1

**JyothiGrace** · 10-19-2021, 10:38 AM

thanks....worked

**Pepe Le Mokko** · 10-19-2021, 10:40 AM

To add my two cents, to Excel 1 is 01/01/1900, so 7 is 07/01/1900 (d/m/y). the MONTH function will always return 1 for numbers up to 31, 2 from 32, to 60 , etc.. ( yes, XL made the mistake of thinking 1900 was a leap year)

**AliGW** · 10-19-2021, 10:43 AM

Thanks for the rep.

Month number to Text formula

LinkBack

Thread Tools

Rate This Thread

Display