Using Text function to obtain "Months"

[RahulCholate]

Hi Guys,

I am Rahul from India.

I want to obtain "Months" (January, February,--etc) using Text function: =Text((1/13/19,"mmmm")) (US Date format)

I am unable to get the answer;

=>Whereas, I am getting answer for =Text((13-01-2019,"mmmm"))

Note; my Dataset is in following format: (1/13/19 15:00) (US Date format). 

Please help me with this error.

[#2]

@RahulCholate 

If you have a date in a cell, say A1, you can use =TEXT(A1,"mmmm")

If you want to specify a literal date, use the DATE function: =TEXT(DATE(2019,1,13),"mmmm")

[#3]

Thanks for you reply Hans,

But how should i convert from format (1/13/19) to a proper Date format, so that i could apply Text function? My complete Dataset consists of improper Date format (1/13/19)!!

Hope i made you clear in understanding.

[#4]

@RahulCholate 

Try

=LET(
    Input,A1,
    Space1,FIND("/",Input),
    Space2,FIND("/",Input,Space1+1),
    Year,MID(Input,Space2+1,4)+2000,
    Month,LEFT(Input,Space1-1),
    Day,MID(Input,Space1+1,Space2-Space1-1),
    Date,DATE(Year,Month,Day),
    TEXT(Date,"mmmm")
)

[#5]

Thank you Hans for the input.

[#6]

@RahulCholate 

Alternatively, with your improper date in A1 (assuming Excel 2021 or 365):

=LOOKUP(--LEFT(A1, SEARCH("/",A1)-1), SEQUENCE(12), TEXT(DATE(2022,SEQUENCE(12),1),"mmmm"))

[#7 · Accepted solution]

@L z. 

Nice approach. Using the same idea:

=INDEX(TEXT(DATE(2022,SEQUENCE(12),1),"mmmm"),LEFT(A1,FIND("/",A1)-1))

[#8]

Even better @Hans Vogelaar

[#9]

Perfect Guys, this is much easy approach. Thanks for your support @Hans Vogelaar , @L z.

[#10]

@RahulCholate 

In response to your PM asking for explaination. As always, best way to understand is to decompose the formula

The logic is the following: you only want to get the MonthName from your improper EN Dates where the month number (1,2...,12) is before the 1st /

#1 A Year consists of 12 months, hence SEQUENCE(12) that returns an array of numbers from 1 through 12. When we nest this SEQ. in the DATE function we get an array of 12 dates (Jan 1st 2022, Feb 1st 2022.... Dec 1st 2022). That new array nested in the TEXT function returns the Month Names only (nb. I added [$-en-GB] in front of mmmm because my Regional Settings aren't EN)

#2 in G2, =LEFT(A2,FIND("/",A2)-1) simply returns what's before the 1st / in A2

#3 We have an array of Month Names in E2:E13. This is a Dynamic Array so we reference it as E2#. So in I2: INDEX(E2#, G2)

Hope this helps