Sometimes dates exported from third-party software are not true dates, causing charting problems — how can this be resolved?
- First, identify whether the date data in Excel are in date format or text format. You can check the cell format to determine this.
- If you find that the cell's Short Date and Long Date formats are the same, then the cellis notin date format and cannot be used for any chronological/date-sequence analysis; we need to make the following adjustments.
- The DATEVALUE function converts dates stored as text into the serial number Excel recognizes as a date
- For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number for the date 2008-1-1
- Please note,computersystem date settings may causeDATEVALUEthe function's result may differ from this example.
- For example, if colleague A's computer system date format is month-day-year, the formula =DATEVALUE("3/4/2008") returns March 4, 2008. But if the same Excel file is opened on colleague B's computer and B's system date format is day-month-year, the formula =DATEVALUE("3/4/2008") will return April 3, 2008.
- Select the date cells that are formatted as text
- Click Data ⇒ Data Tools ⇒ Text to Columns
- Choose Delimited ⇒ Next ⇒ leave all options unchecked
- Column date format: Date: YMD (depending on the original date's year-month-day order)
- Finish
