PHP: How to convert VARIANT DATE to datetime string

Question

In my project, I get from external system date & time in VARIANT DATE type .

I need to convert it to datetime, i.e., 44768.25 => 2022-07-26 06:06:40.

Do you know how to do it in PHP? What is the best approach?

`44768.25` <--- I suspect this is coming from an excel sheet? — nice_dev, May 16 '22 at 08:17
Does this answer your question? [Excel date conversion using PHP Excel](https://stackoverflow.com/questions/11119631/excel-date-conversion-using-php-excel) — jpgerb, May 16 '22 at 19:18

score 2 · Answer 1 · answered May 16 '22 at 09:46

Use this formula to change from Excel date to Unix date,

then you can use "gmdate" to get the real date in PHP:

UNIX_DATE = (EXCEL_DATE - 25569) * 86400

The 86400 is the number of seconds in a day = 24 * 60 * 60.

The 25569 is the number of days from Jan 1, 1900 to Jan 1, 1970.

Excel base date is Jan 1, 1900 and Unix is Jan 1, 1970.

UNIX date values are in seconds from Jan 1, 1970 (midnight Dec 31, 1969).

So to convert from excel you must subtract the number of days and then convert to seconds.

$EXCEL_DATE = 44768.25;
$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo gmdate("d-m-Y H:i:s", $UNIX_DATE);

You will get output as 26-07-2022 06:00:00

1 Answers1