Text function not returning correct month

[SanjayGMusafir]

Hi Experts
Just now I encountered a strange situation.

The TEXT function is not returning correct month name from a date. See Column D & E below

I must be doing something really silly and ignoring something basic to get into this trouble.

Please help
Thanks in Advance

Book1
	B	C	D	E
2	Date	Month	Text	Check
3	10/31/24	10	Jan	1
4	11/30/24	11	Jan	1
5	12/31/24	12	Jan	1
6	1/31/25	1	Jan	1
7	2/28/25	2	Jan	1
8	3/31/25	3	Jan	1
9	4/30/25	4	Jan	1
10	5/31/25	5	Jan	1
11	6/30/25	6	Jan	1
12	7/31/25	7	Jan	1
13	8/31/25	8	Jan	1
14	9/30/25	9	Jan	1
Sheet1

Cell Formulas
Range	Formula
B3:B14	B3	=EOMONTH(TODAY(),SEQUENCE(12,,0,1))
C3:C14	C3	=MONTH(B3)
D3:D14	D3	=TEXT(MONTH(B3),"mmm")
E3:E14	E3	=TEXT(MONTH(B3),"m")
Dynamic array formulas.

 

[SanjayGMusafir]

Just realized my mistake.

I Changed formula to (as below) and it worked.

=TEXT(B3,"mmm")

 

[Alex Blakenburg]

That's not now the the formula works.
D should be

=TEXT(B3,"mmm")

E should be

=TEXT(B3,"m")

 

[SanjayGMusafir]

Thanks @Alex Blakenburg it worked.

Unable to mark your answer as solution. Must be some system glitch.

Thanks again.

 

[Alex Blakenburg]

All good. I think you realised at the same time as I posted. Let me know if you need an explanation.

 

[SanjayGMusafir]

All good. I think you realised at the same time as I posted. Let me know if you need an explanation.

Yes explanation would be helpful.

Thanks

 

[SanjayGMusafir]

All good. I think you realised at the same time as I posted. Let me know if you need an explanation.

@Alex Blakenburg Correct me if I'm wrong. When we convert date to months it returns numbers from 1 to 12 and Excel while passing it through text function assumes to be day 1 to day 12 thus returning month to be January.

But when we pass the entire date through text function, it returns month of that date.

Silly mistake on my part.

Thanks you offered me an explanation. It gave me a moment to rethink what I was doing wrong.

 

[Alex Blakenburg]

You are correct. The text function assumes the first parameter is a date and then uses that to figure out the components requested eg m, mmm, y, yyy etc
The month function in the first parameter means the text function is only seeing 1-12 and it is interpreting that as being 01-Jan-1900 - 12-Jan-1900.