Forum Discussion
Solved
@Converting date formats
Hi, This should be an easy task to fix. I have a range of dates in four digit year and two digit month such as 199903. How do I convert to Mar-1999 Many thanks,
TFMcNeil Assuming the cell contains the actual number 199903 (or the text string "199903"), use the DATE function with LEFT and RIGHT to extract the year and month. For example:
=TEXT(DATE(LEFT(A1, 4), RIGHT(A1, 2), 1), "mmm-yyyy")Note: the TEXT function was used in this example to return the text string "Mar-1999". If you want the results to be stored as an actual date, remove the TEXT function and apply custom cell formatting (mmm-yyyy) to the results range.
TFMcNeil Assuming the cell contains the actual number 199903 (or the text string "199903"), use the DATE function with LEFT and RIGHT to extract the year and month. For example:
=TEXT(DATE(LEFT(A1, 4), RIGHT(A1, 2), 1), "mmm-yyyy")
Note: the TEXT function was used in this example to return the text string "Mar-1999". If you want the results to be stored as an actual date, remove the TEXT function and apply custom cell formatting (mmm-yyyy) to the results range.
Thank you. After formatting a new column, I pasted the results of the above formula and into the new column and saved the worksheet.
Thank you,
Best.
TFMcNeil You're welcome 🙂
