Operation process for importing Unix format timestamps into Excel

Is it difficult to import Excel timestamps into Unix format? PHP editor Baicao found that when converting timestamps in Excel to Unix format, technical problems are often encountered. In order to solve this problem, this article will introduce in detail the complete process of Excel importing Unix format timestamps, including how to extract timestamps from cells, handle dates in different formats, and use PHP code for conversion. By reading this article, developers can master the best practices to solve this problem and easily convert Excel timestamps and Unix format timestamps to and from each other.

Excel does not support Unix format timestamps by default, which is very inconvenient when importing data. The following formula can be used to convert the timestamp into time in Excel format:

where x is the cell of the timestamp, and 8 in 8*3600 is the time zone of China. Then set the formula cell to date time format. The conversion result is as shown in the figure:

The principle of this formula: The date in Excel is actually a serial value. It uses 1900-1-1 as 1, and the serial value increases by 1 every day. The Unix timestamp is the number of seconds that have passed since 1970-1-1 0:00:00 UTC. Using As 1, then 18 should be added to the final formula. Why should 19 be added? This is a bug in Excel - 1900 is also considered a leap year, so an extra day should be added.

Also note that in Excel's Tools->Options->Recalculation, there is a 1904 date system. If this option is checked, the above formula should change 70 to 66, that is, as follows As shown in the picture

The above is the detailed content of Operation process for importing Unix format timestamps into Excel. For more information, please follow other related articles on the PHP Chinese website!