Text function not returning correct month

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,503
Office Version
  1. 2021
Platform
  1. MacOS
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
BCDE
2DateMonthTextCheck
310/31/2410Jan1
411/30/2411Jan1
512/31/2412Jan1
61/31/251Jan1
72/28/252Jan1
83/31/253Jan1
94/30/254Jan1
105/31/255Jan1
116/30/256Jan1
127/31/257Jan1
138/31/258Jan1
149/30/259Jan1
Sheet1
Cell Formulas
RangeFormula
B3:B14B3=EOMONTH(TODAY(),SEQUENCE(12,,0,1))
C3:C14C3=MONTH(B3)
D3:D14D3=TEXT(MONTH(B3),"mmm")
E3:E14E3=TEXT(MONTH(B3),"m")
Dynamic array formulas.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Just realized my mistake.

I Changed formula to 👇 (as below) and it worked.
Excel Formula:
=TEXT(B3,"mmm")
 
Upvote 0
That's not now the the formula works.
D should be
Excel Formula:
=TEXT(B3,"mmm")
E should be
Excel Formula:
=TEXT(B3,"m")
 
Upvote 1
Solution
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.
 
Upvote 0
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.
 
Upvote 1

Forum statistics

Threads
1,223,880
Messages
6,175,157
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top