PHP date() to Excel serial format conversion

Question

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.

Here you'll see how excel does calculate the serial date

B001ᛦ
– B001ᛦ

2019-04-18 11:02:07 +00:00
Commented Apr 18, 2019 at 11:02 — B001ᛦ
– B001ᛦ, Commented Apr 18, 2019 at 11:02

MorganFreeFarm · Accepted Answer · 2019-04-18 11:41:05Z

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)

edited Apr 18, 2019 at 11:41

answered Apr 18, 2019 at 11:10

MorganFreeFarm

3,7318 gold badges26 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

al1en Over a year ago

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?

MorganFreeFarm Over a year ago

Xmm, i'm not sure about that, let me check

MorganFreeFarm Over a year ago

Is it exactly one hour ? if it is, what about to add one hour manually ?

al1en Over a year ago

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

Collectives™ on Stack Overflow

PHP date() to Excel serial format conversion

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related