Let’s talk about how to convert excel timestamp to php timestamp

In our daily work, we need to process time data in Excel and convert it to PHP timestamps for use in websites. This article will show you how to convert timestamps in Excel to PHP timestamps.

First, we need to understand Excel’s timestamp format. Dates and times in Excel are stored as "serial numbers", which are the sum of the number of days and fractional parts of the time from January 1, 1900 to the current date. For example, the serial number for January 1, 2019 is 43466.

Next, we need to understand the timestamp format in PHP. A timestamp in PHP is an integer representing the number of seconds since January 1, 1970 00:00:00 UTC to the current time.

In Excel, we can use the following formula to convert date to timestamp:

=(A1-DATE(1970,1,1))*86400

where , A1 is the cell that stores the date in Excel, and "DATE(1970,1,1)" represents the serial number of the date calculated from January 1, 1970. 86400 represents the number of seconds in a day.

For example, if the date stored in cell A1 is "2019/1/1 0:00", the formula to convert this date to a timestamp is:

=(A1-DATE (1970,1,1))*86400

The calculation result is:

1546300800

Among them, 1546300800 means from January 1, 1970 00:00:00 UTC The number of seconds since January 1, 2019 00:00:00 UTC.

In PHP, we can use the following code to convert timestamp to date:

$date = date("Y/m/d H:i", $timestamp);

Among them, $timestamp is the PHP timestamp, indicating the number of seconds from January 1, 1970 00:00:00 UTC to the current time. The date() function converts a timestamp into a date string in a specified format.

For example, if we want to convert the above timestamp "1546300800" into a date string, we can use the following code:

$date = date("Y/m/d H: i", 1546300800);

The calculation result is:

2019/01/01 00:00

In addition, if the timestamp in Excel does not contain the date part, only Containing the time part, we can convert it to datetime format before calculation. The specific method is to select "Custom" in the cell format, and then enter "yyyy/m/d h:mm:ss" or "yyyy-m-d h:mm:ss".

To summarize, converting Excel timestamps to PHP timestamps can be done by following these steps:

1. To convert dates to timestamps in Excel, use the formula:

=(A1-DATE(1970,1,1))*86400

2. To convert timestamp to date string in PHP, use code:

$date = date("Y/m/d H:i", $timestamp);

The above are the detailed steps to convert Excel timestamp to PHP timestamp. Hope it helps.

The above is the detailed content of Let’s talk about how to convert excel timestamp to php timestamp. For more information, please follow other related articles on the PHP Chinese website!