PHP import Excel time format converted to Delphi timestamp? How to solve it?

Detailed explanation of PHP import Excel date format conversion: Solve the Delphi time stamp problem

When importing Excel (xlsx) files using PHP, it is often encountered that date and time data are converted to Delphi timestamps (such as 44845). This non-standard format brings difficulties to data processing. This article will analyze the root cause of the problem and provide a PHP solution to convert Delphi timestamps to the commonly used YYYYMMDD format.

Problem description:

After importing data from Excel, the date time field appears as a Delphi timestamp similar to "44845", rather than the expected date format. This is related to the date storage mechanism of Excel or WPS software.

Solution:

The core is to convert the Delphi timestamp to a standard Unix timestamp and then format it. The following provides a PHP function formatTime :

 function formatTime($timevalue) {
  if (strpos($timevalue, "-")) {
    return strtotime($timevalue); // Standard date format, use strtotime directly
  } else {
    return interval(($timevalue - 25569) * 3600 * 24); // Delphi timestamp conversion}
}

This function determines whether the input $timevalue contains "-". If "-" is included, it is considered as a standard date format (for example, "YYYY-MM-DD"), and is converted directly with strtotime() ; otherwise, it is considered as a Delphi timestamp, and is converted using the formula ($timevalue - 25569) * 3600 * 24 (25569 is the base date difference between the Delphi timestamp and the Unix timestamp, that is, December 30, 1899). intval() ensures that the result is an integer.

How to use:

 $delphiTimestamp = 44845;
$unixTimestamp = formatTime($delphiTimestamp);
$yyyymmdd = date('Ymd', $unixTimestamp); // Use Ymd to get the YYYYMMDD format echo $yyyymmdd;

This method first converts the Delphi timestamp to a Unix timestamp, and then uses date('Ymd', $unixTimestamp) to format it into YYYYMMDD format, effectively solving the format conversion problem in data processing. Note that we used Ymd instead of ymd to ensure that the year is represented by four digits.

The above is the detailed content of PHP import Excel time format converted to Delphi timestamp? How to solve it?. For more information, please follow other related articles on the PHP Chinese website!