Unable to extract month from date

[milo1234]

Hi, I have used the correct formula =TEXT(I2, "mmm") however the month is not being extracted from the date. The start date column has a data type of 'Date' and the Month column has a data type of 'Custom' to "mmm". Please help? 

[#2]

@milo1234 

Set the number format of the Month column to General.

The values in the Start Date column are left-aligned. This makes me suspect that they are text values, not 'real' dates. What happens if you select (for example) I2, press F2 and then press Enter?

[#3 · Accepted solution]

@milo1234 

Most probably Start Dates are texts which only looks like dates, not dates (which are actually numbers). The rest depends on which is default date format on your machine.

As variant you may transform texts to dates with Data->Text to Columns selecting MDY on third step.

Or

=TEXT( DATE(2021, LEFT([@[Start Date]],2), 1), "mmm")

[#4]

Hi @Hans Vogelaar
I have changed the Month column to General and this does nothing.
Also when I select I2 press F2 and press enter, nothing changes.

[#5]

@milo1234 

Could you attach a copy of the workbook without sensitive/proprietary information?

[#6]

@Hans Vogelaar 

Please find test data attached, 

[#7]

@milo1234 

Sergei Baklan already suggested using Data > Text to Columns.

Here is the result. I expanded the formulas to prevent them from returning "Jan" if the date has not been filled in.

[#8]

Thank you so much Hans!

[#9]

@Hans Vogelaar Hi, I have updated my data in the spreadsheet and when I go to Data > Text to Columns, the column changes the text to the actual formula.
Am I doing something wrong? 

[#10]

@milo1234 

Make sure the column is not formatted as Text.

If that is not the cause, make sure that the Show Formulas button on the Formulas tab of the ribbon is NOT highlighted.

[#11]

Hi Hans,

The column is formatted to General and the Show Formulas button is NOT highlighted. Still have the same problem

[#12]

I've fixed the issue - please ignore. Thank you

[#13]

@milo1234 Stupid of Xcel didn't understand the leading of the cell is 'space'. Remove the space then it works