How to Format Extended Report Dates in Microsoft Excel Date Format

How to convert Extended Report dates into Microsoft Excel date format

1. Create an extended report with one or more date fields.
2. Click Export to export the report into Microsoft Excel.
3. Copy and paste the following formula into an empty column in Excel. The formula will convert dates from column D starting on row 2. If the date is in a different column or there is a different starting, the variables will need to be modified.

   

   • For program year 2013 and later, the formula is:

   =IF(LEN(D2)=7,DATE(RIGHT(D2,4),LEFT(D2,1),MID(D2,2,2)), DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,3,2)))
   

   • For program year 2012 and earlier, the formula is:

   :=IF(LEN(D2)=7,DATE(RIGHT(D2,4),LEFT(D2,1),MID(D2,2,2)), DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,2,2)))
   

4. Double click the small square at the bottom right of the field to apply the formula to all rows.
   
   

   

5. Right click the column header and then click Format Cells.
   
   

   

6. Select Date and highlight the desired formatting
   
   

   

7. Click OK to save the changes.

The dates will now appear in a Microsoft Excel format.

Article ID: 241
Created: Mon, Jul 25, 2016
Last Updated: Tue, Dec 27, 2016

Online URL: //kb.erosupport.com/article-241.html