Excel 365 - TEXT function oddity

An Excel 365 "oddity"

I'm trying to get the month value from a date displayed as the month name, e.g. 1 -> January, 2 > February, etc.



The image shows, I hope my problem. The month displayed using the TEXT function is always January. When I tried the TEXT function on the day and year values I got similarly odd results.

I'm clearly doing something wrong, or misunderstanding something. It seems it's getting a value of 1 from somewhere, perhaps owing to my misunderstanding of things - though where "July" came from I really don't know.

Any thoughts?

My understanding of the Text function is that it is used directly on the date you are manipulating rather than on a pure number extracted from it.
The Text function takes something that has a special meaning [such as a date] and presents it as simple text.

This works

Code:

=TEXT(B2,"mmmm")

March
as do

Code:

=TEXT(B2,"dd")

29

Code:

=TEXT(B2,"yyyy")

2024


What you were doing was making the Text function count the number of days in the year from the value Month(B2) i.e. three and tell you which month that is in.
If you overwrite

Code:

=TEXT(B2,"mmmm")

with

Code:

=TEXT(45,"mmmm")

you will get the response
February
or
if you overwrite

Code:

=TEXT(B2,"mmmm")

with

Code:

=TEXT(365,"mmmm")

you will get the response
December
or
if you overwrite

Code:

=TEXT(B2,"mmmm")

with

Code:

=TEXT(2024,"mmmm")

you will get the response
July
[i.e. the month that is 2024 days from the start of a year]


All the best
Denis

Thanks guys, that's cracked it. I needed the actual month name to put into a concatenated string with other text and extracted values.