Formula for Date Name

[Iccreamann21]

Hello, Good Morning, Happy 2024,

Quick questions, the data I am exporting has the column "Closed Date" and what I'm looking to do is extract just the name of the month.

Closed Date	Month Name
12.21.2023	December

Currently, I am doing this way and it works, but Im trying to eliminate having to add the cheater cell and the AA:AB table.

Closed Date	Cheater	Month Name
12.21.2023	=left(A2,2)	=vlookup(B2,AA:AB,2,0) <- then create a table in column in AA:AB

Any help will be appreciated. Thank you

 

[awoohaw]

apologies last question on this, (I did try to work out this out but to no avail) on the formula that was provided by @Fluff why is it if i was to change the "mmmm" to "yyyy" this gives me 2027 ?

I would have assumed to see 2024 as this what was entered for the year aspect within the DATE function

View attachment 104674

What @Fluff has done is strip out the year from a date that was input as text ("2024/12/21"). But the excel date value of that date is 45647.
The left two characters of that number are 45. So the DATE function in the formula inserts 45 as the months argument is calculated using:
DATE(2024,45,1) which calculates to 2024/09/01. Then the text conversion formula changes that date value to 4 digit years, so "2027".
If you use the exact same data in cell A2 but ask for the "mmmm" long month name you'll get SEPTEMBER and not DECEMBER.

what is below does not have the checks for cell being blank or not.

Book1
	A	B	C	D	E	F
1
2	Entered as a date:	Date Value	Left 2	DATE(y,m,d)
3	12/21/2024	45647	45	2027-09-01	2027	September
4
5	Entered as text	cell contents	left 2	Date(y,m,d)
6	12/21/2024	12/21/2024	12	2024-12-01	2024	December
Sheet1

Cell Formulas
Range	Formula
B3,B6	B3	=A3
C3,C6	C3	=LEFT(A3,2)
D3,D6	D3	=DATE(2024,LEFT(B3,2),1)
E3,E6	E3	=TEXT(DATE(2024,LEFT(B3,2),1),"yyyy")
F3,F6	F3	=TEXT(DATE(2024,LEFT(B3,2),1),"mmmm")

 

[Fluff]

I would have assumed to see 2024 as this what was entered for the year aspect within the DATE function

Firstly the formula was written for the OP who had a text date with the month as the left 2 digits. By the looks of things you have a real date which makes a very big difference & even if your date was text you have the day as the 1st 2 digits not the month.

 

[Arts]

@awoohaw appreciate the detailed the response, I shall go over this as it seems to be going over my head at the min (put it down to rust as not having used Excel over the christmas/new years period) never really had an issue with dates in excel but this seems to have thrown me but shall read over your example, thanks once again

 

[Arts]

Firstly the formula was written for the OP who had a text date with the month as the left 2 digits. By the looks of things you have a real date which makes a very big difference & even if your date was text you have the day as the 1st 2 digits not the month.

Hi Fluff yeah I did notice the OP had used American date format but I was using the year as an example as this was in the same location whether US or UK format but shall go over the response provided by awoohaw as I think its more an understanding of the manner of how the date is entered, as text etc

as always appreciate the response

 

[Rick Rothstein]

Here is one more solution to the original question for this thread...

=IFERROR(TEXT(28*LEFT(A2,2),"mmmm"),"")