Dates are displaying as time in Excel when viewing Historical Extract

Products: Accelerated Reader, Accelerated Math, Star Early Literacy, Star Math, Star Reading, Star CBM, Star Custom

Platform: Macintosh, Windows

Last Updated: 8/5/2022

Issue

When double-clicking on the Historical Extract .csv file and opening in Excel, Excel displays only the time component of the dates (00:00:0) included in the extract.

Reason

Historical extracts are comma-separated value (.csv) files that can be opened in a spreadsheet program. If you open the Historical Extract in Excel and save without changing the format of the date columns (either Save or Save As), the date columns in the file will only contain minutes, seconds, and milliseconds (00:00:0) going forward. You will need to format the date columns before saving. Times will be shown in Coordinated Universal Time (UTC).

NOTE: When opening Historical Extracts in Notepad or Notepad++, importing to Google docs, or importing into Excel (open Excel > Data tab > Get Data > From Text/CSV > browse to and select the Historical Extract) the dates show correctly.

Resolution

Use these steps to format the date columns before saving in Excel.

1. Select the column or columns.
2. Right-click on the selected area.
3. Choose Format Cells.
4. Select Date on the Number tab.
5. Choose a date format.
6. Click OK.
7. Save the .csv file.

Date columns that need formatting if you want to open in Excel:

• School Year Start Date
• School Year End Date
• Birthdate
• Activity Completed Date
• Screening Window Start
• Screening Window End
• Window SGP Prior Test 1 Date
• Window SGP Prior Test 2 Date