PHP import Excel date format conversion problem: How to convert Delphi timestamp to yymmdd format?

Detailed explanation and solution for PHP import Excel date format conversion

When importing Excel files (such as XLSX format) using PHP, you often encounter date format conversion problems. Date and time data in Excel tables may be stored in non-standard formats such as Delphi timestamps and needs to be converted into the commonly used "yymmdd" format. This article provides an effective PHP solution.

Problem description:

When PHP imports XLSX files, the date and time data may be presented in special formats such as Delphi timestamps, which does not match the expected "yymmdd" format.

Solution:

To solve this problem, we can create a PHP function to handle conversions in different date and time formats. This function is able to identify and process standard date-time formats (for example: yyyy-mm-dd) and Delphi timestamp formats.

Here is a PHP function that handles both cases:

 function formatExcelDate($timevalue) {
  if (strpos($timevalue, '-') !== false) { // Check whether '-' is included and determine whether it is the standard date format return date('ymd', strtotime($timevalue)); // Use strtotime to convert and format} else {
    $unixTimestamp = intval(($timevalue - 25569) * 3600 * 24); // Convert Delphi timestamp to Unix timestamp return date('ymd', $unixTimestamp); // Format as yymmdd
  }
}

This function receives a time value $timevalue as a parameter. It first checks whether the value contains "-", if it is included, it is considered a standard date and time string, and directly converts it to a Unix timestamp using the strtotime function, and then formats it as "yymmdd" with date('ymd', ...) . Otherwise, treat it as a Delphi timestamp, convert it to a Unix timestamp using the formula, and format it as "yymmdd".

Example of usage:

 $delphiTimestamp = "44775"; // Delphi timestamp example $standardDate = "2024-10-26"; // Standard date format example $formattedDate1 = formatExcelDate($delphiTimestamp);
$formattedDate2 = formatExcelDate($standardDate);

echo "Delphi timestamp conversion result: " . $formattedDate1 . "\n"; // Output yymmdd format echo "standard date format conversion result: " . $formattedDate2 . "\n"; // Output yymmdd format

This code demonstrates how to convert Delphi timestamps and standard date formats using the formatExcelDate function, and output the result to "yymmdd" format. Note that this function assumes that the Delphi timestamp is calculated from 1899-12-30. If your Delphi timestamp start date is different, you need to adjust the 25569 value in formula intval(($timevalue - 25569) * 3600 * 24);

Through this function, you can easily convert various date and time formats encountered during Excel import into a unified "yymmdd" format, improving data processing efficiency.

The above is the detailed content of PHP import Excel date format conversion problem: How to convert Delphi timestamp to yymmdd format?. For more information, please follow other related articles on the PHP Chinese website!