Forum Discussion
Date help
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
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...
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.
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.
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...
