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? — mighty_dev
– mighty_dev, Commented May 16, 2022 at 8:17
Does this answer your question? Excel date conversion using PHP Excel — jpgerb
– jpgerb, Commented May 16, 2022 at 19:18

Robert S · Accepted Answer · 2022-05-16 09:46:35Z

2

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

answered May 16, 2022 at 9:46

Robert S

8662 gold badges10 silver badges28 bronze badges

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

1 Answer 1