2

I've looked for hours now. Everywhere I see examples of how to convert the Excel serial date number to UNIX_Date.

What I want to do is a PHP date("Y-m-d H:i:s") to convert it to Excel serial date number.

Example:

$php_date = date("Y-m-d H:i:s"); // "2019-04-18 11:57:25" at the time

and if I type this "2019-04-18 11:57:25" in Excel cell, and format it to show a number then Excel shows me "43573.4982060185" How to get this "43573.4982060185" in PHP?

EDIT: As per Morgan's answer bellow I have a function now that does this: EDIT 2: Added timezone set to get my local time, if anyone else needs this, set for your local timezone. List of supported timezones for PHP can be found here https://www.php.net/manual/en/timezones.php

function PHP_to_Excel() {
date_default_timezone_set("Europe/London");
$datetime = date("Y-m-d H:i:s");    
$strdate = strtotime($datetime);    
$excel_date = floatval(25569 + ($strdate / 86400));

return $excel_date;
}

The above function will return the correct serial number for the current local date and time.

al1en
  • 511
  • 1
  • 5
  • 28
  • [Here](https://support.office.com/en-us/article/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252) you'll see how excel does calculate the serial date – B001ᛦ Apr 18 '19 at 11:02

1 Answers1

1

Here:

<?php

date_default_timezone_set('America/New_York');
$date_time = date("Y-m-d H:i:s");
$str_date = strtotime($date_time . ' +1 day');
$excel_date = floatval(25569 + $str_date / 86400);
var_dump($excel_date);

You should change time zone with your current, you can see all timezones here

Return:

float(43573.46537037)
MorganFreeFarm
  • 3,811
  • 8
  • 23
  • 46
  • 1
    You're very close, I've also tried similar approach but without floatval(). But in your case now, if I type that value in excel, excel shows an approximate one hour difference. Is there a way to fix this behaviour? Or explanation why is it happening? – al1en Apr 18 '19 at 11:19
  • Xmm, i'm not sure about that, let me check – MorganFreeFarm Apr 18 '19 at 11:24
  • Is it exactly one hour ? if it is, what about to add one hour manually ? – MorganFreeFarm Apr 18 '19 at 11:29
  • Well I thought of that, but I'm not sure if the same code will give me correct time during summer/winter time. This might be related with that. But I guess I cannot check now – al1en Apr 18 '19 at 11:31