Changing the date format in Excel reports

Changing the date format in Excel reports

Symptoms

When you export data into a flat file (CSV), the timestamp value format in the exported file is unsupported.

Resolving the problem

To change the date format in the exported file to the default date format (yyyy-mm-dd hh:mm:ss.000), convert the timestamp value by using the following formula:

=TEXT(<source_cell>/1000/86400+70*365+19,"yyyy-mm-dd hh:mm:ss.000")

To change the timestamp value in cell A1 to the default format in cell B1, use the following formula in cell B1:

=TEXT(A1/1000/86400+70*365+19,"yyyy-mm-dd hh:mm:ss.000")
Formula description
  • A1 is the source cell.
  • +70*365 adds 70 years in the range 1900 - 1970 because the time series of excel starts with 1900-1-1 as 1, but Unix timestamp starts from 1970-1-1 0:00:00.
  • +19 adds 19 days (17 leap years in the range 1900 - 1970 + 1900-1-1 as 1 + a bug of excel that classifies 1900 as a leap year).

Result: Cell B1 displays the timestamp value in UTC time (with no time zone). For example, if the timestamp value in cell A1 is 1631699907297, then cell B1 displays the timestamp value in UTC time (with no time zone).2021-09-15 09:58:27.297

To change the value to Beijing time (UTC +8), use the following formula in cell B2:

=TEXT((A2/1000 + 8 * 3600)/86400+70*365+19,"yyyy-mm-dd hh:mm:ss.000")
Formula description
  • A2 is the source cell.
  • +8*3600 changes time to Beijing time (UTC +8).
  • +70*365 adds 70 years in the range 1900 - 1970 because the time series of excel starts with 1900-1-1 as 1, but Unix timestamp starts from 1970-1-1 0:00:00.
  • +19 adds 19 days (17 leap years in the range 1900 - 1970 + 1900-1-1 as 1 + a bug of excel that classifies 1900 as a leap year).

Result: Cell B2 displays the timestamp value in Beijing time (UTC +8).