Date help

[anupambit1797]

Dear Experts,

                    I have an issue like this, given a Column "A" with the dates, my settings are like this,

Text function to extract is not helping somehow in "B2" to get the Month for each row.

Thanks in Advance,

Br,

Anupam

[#2]

@anupambit1797 

Actually you have texts which looks like datetime. In Excel the latest are numbers, not texts.

If you need only month names, they could be taken as

=TEXT(29*MID(A2,4,2), "mmm")

please see attached.

[#3]

Thanks a lot @SergeiBaklan , could you please share what's "29" here?

Thanks in advance,

Br,

Anupam

[#4]

@anupambit1797 

MID(A2,4,2) returns month number (1,2,...12) in text form. In Excel dates are actually sequential numbers where 1 is equal to Jan 01, 1900. So, Jun 11 2024 means number 45454.

Multiplying month numbers from 1 to 12 on 29 we return days within Jan to Feb in year 1900

Formatting the result as "mmm" we have the month name.

Same works for 28 or 30 multipliers.

[#5 · Accepted solution]

@anupambit1797 One fairly common method for converting text strings to date values is to use the DATE function. For example:

=DATE(MID(A2,7,4), MID(A2,4,2), LEFT(A2,2))

From there you can apply custom date formatting to the results as desired.

If you're only interested in extracting the month in "mmm" format, input 1 for both the year and day arguments (year 1 is interpreted as 1901), then use the TEXT function to convert the results:

=TEXT(DATE(1, MID(A2,4,2), 1), "mmm")

Another standard method for converting month numbers to names is the CHOOSE function. For example:

=CHOOSE(MID(A2,4,2), "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

While this method is obviously the longest formula to type out, it requires the fewest number of operations to return the desired result.

Note: if you are using a modern version of Excel that supports spilled arrays, the entire range (A2:A352) can be referenced to spill the results with a single formula for any of the above-mentioned methods.

Please see the attached workbook, which also contains additional examples...