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 Research
- CityDreamer: Compositional Generative Model of Unbounded 3D Cities
- Efficient Regional Memory Network for Video Object Segmentation
- Toward 3D Object Reconstruction from Stereo Images
- GRNet: Gridding Residual Network for Dense Point Cloud Completion
- Pix2Vox: Context-aware 3D Reconstruction from Single and Multi-view Images
Recent Posts
Categories
- C++ (3)
- Java (18)
- JavaScript (1)
- Machine Learning (8)
- Network (4)
- Others (29)
- PHP (11)
- Python (5)