Excel stores a date internally as a number of days since January 1, 1900.

For example: "June 9th, 2011 10:30 AM" would be stored as "40703.4375".

40703 is the number of full days since 01/01/1900 and 0.4375 represents the time (10.5/24 = 0.4375).

When you process dates read from an Excel spreadsheet (e.g. using PHPExcel) you often want to convert them into a UNIX timestamp i.e. a number of seconds elapsed since midnight of January 1, 1970 UTC.

Here is a PHP code to do that:

// Numbers of days between January 1, 1900 and 1970 (including 19 leap years) define("MIN_DATES_DIFF", 25569); // Numbers of second in a day: define("SEC_IN_DAY", 86400); function excel2timestamp($excelDate) { if ($excelDate <= MIN_DATES_DIFF) return 0; return ($excelDate - MIN_DATES_DIFF) * SEC_IN_DAY; }

Although the code above is written in PHP the function should be very similar in any other language e.g. C# or java. If the provided date is earlier than 1/1/1970 then the minimal timestamp value will be returned.

### Alternative solution:

If you provide the Excel spreadsheet that you, later on, read from in your app you could add a hidden cell that would calculate the timestamp for you, within the spreadsheet.

Assuming that B2 is the cell that stores your date the formula for calculating the timestamp would be:

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

Now you only need to read the calculated value from the hidden cell.

###### Recent Posts

###### Categories

- C++ (3)
- Java (18)
- JavaScript (1)
- Machine Learning (8)
- Network (4)
- Others (27)
- PHP (11)
- Python (5)