SOLVED

# @Converting date formats

Copper Contributor

# @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,

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

# Re: @Converting date formats

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

# Re: @Converting date formats

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.

# Re: @Converting date formats

@TFMcNeil You're welcome :)

1 best response

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

# Re: @Converting date formats

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