How to Change Text Like YYYYMMDD into YYYY/MM/DD format?

Often, you get text like 19950607 and you want to be able to convert it into text like 1995/06/07.  Here’s a formula you can use to convert the text into

a formula. (this assumes the text is in A1)

=DATE(MID(A1,1,4), MID(A1,5,2), MID(A1,7,2))

What this does is this:

Date formula has the following parameters – DATE (Year, Month, Day)

The formula I wrote about simply splits the text

into Year, Month and Day and uses it in the DATE function.

MID function allows you to split text and pick up parts of it. Here’s the syntax

MID(text, start number of digit you want to start from, number of digits)

MID(A1,7,2) – picks up the text 19950607 (i.e. A1) starts from 7 i.e. 0 and gives you two digits from there – 07


Excel/Financial ModelingProfitability/Cost AnalysisDashboard ReportingCustom Invoice

Leave a Reply

Your email address will not be published. Required fields are marked *