SOLVED

@Converting date formats

Copper Contributor

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,

 

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@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.

@djclements 

 

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 :)

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@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.

View solution in original post