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

 

[Iccreamann21]

How about

=IF(A2="","",TEXT(DATE(2024,LEFT(A2,2),1),"mmmm"))

Money in the bank! That worked like a champion. You are the real MVP!

This doesn't matter as much, but how do I get the month name to show in all uppercase?

 

[Iccreamann21]

Another option which might work for OP

=TEXT(SUBSTITUTE(A1,".","/"),"mmmm")

I tested this and it works as well! 2 solid solutions

 

[Fluff]

For caps use

=IF(A2="","",UPPER(TEXT(DATE(2024,LEFT(A2,2),1),"mmmm")))

 

[awoohaw]

At work, I can't use XL2BB.

I did not. What I have is 12/21/2023 Date format. Cell A2 is in general format, still showing "December" as OP wanted. Cell D1 just includes

=FORMULATEXT(B1)

Edit. I see my mistake. OP is using 12.21.2023 as opposed to 12/21/2023. My bad!

I was equally bad. I though your post was from the OP! Best wishes.

 

[Dave Patton]

You can use a lookup
With E2, you do not need the Lookup table.

T202401a.xlsm
	A	B	C	D	E	F	G	H
1			Closed Date	Column4			1	JANUARY
2			12.21.2023	DECEMBER	DECEMBER		2	FEBRUARY
3			01.21.2024	JANUARY	JANUARY		3	MARCH
4							4	APRIL
5							5	MAY
6							6	JUNE
7							7	JULY
8							8	AUGUST
9							9	SEPTEMBER
10							10	OCTOBER
11							11	NOVEMBER
12							12	DECEMBER
13
1g

Cell Formulas
Range	Formula
D2:D3	D2	=LOOKUP(--LEFT(C2,2),$G$1:$G$12,$H$1:$H$12)
E2:E3	E2	=LOOKUP(--LEFT(C2,2),{1;2;3;4;5;6;7;8;9;10;11;12},{"JANUARY";"FEBRUARY";"MARCH";"APRIL";"MAY";"JUNE";"JULY";"AUGUST";"SEPTEMBER";"OCTOBER";"NOVEMBER";"DECEMBER"})

 

[Arts]

A quick question on this (I was looking at this trying to solve) would anyone know why when I try to do a search for the "/" this gives me #VALUE

In my mind (probably the wrong approach) I was hoping to find the month value of 09 by doing a search and then attaining the month this way but tripped up at the first hurdle when I noticed the SEARCH function wouldn't pick up the "/"

 

[Fluff]

I f the date is a real date, then the / is just part of the format & does actually exist.

 

[Arts]

Thanks Fluff! (assume you mean doesn't exist)

 

[Fluff]

I do, typo on my part.

 

[Arts]

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