Magento Expert Forum - Improve your Magento experience

Results 1 to 2 of 2

Convert Excel dates into PHP timestamps

  1. #1
    Junior Member ccvv's Avatar
    Join Date
    Mar 2013
    Posts
    66
    Thanks
    6
    Thanked 17 Times in 13 Posts

    Smile Convert Excel dates into PHP timestamps

    Having trouble getting your PHP script to build dates from Excel using mktime() ?  Me too. Everything’s one day off?  I know it.  Here’s what I found.

    The proper way to convert Excel dates into PHP-friendly timestamps using mktime():

    PHP Code:
    // The date 6/30/2009 is stored as 39994 in Excel
    $days 39994;

    // But you must subtract 1 to get the correct timestamp
    $ts mktime(0,0,0,1,[B]$days-1[/B],1900);

    // So, this would then match Excel’s representation:
    echo date(“m/d/Y”,$ts); 
    Excel uses “number of days since Jan. 1, 1900″ to store its dates.  It also treats 1900 as a leap year when it wasn’t, thus there is an extra day which must be accounted for in PHP (and the rest of the world).  Subtracting 1 from Excel’s number will fix this problem.

    View more threads in the same category:


  2. #2
    Junior Member jaredovi's Avatar
    Join Date
    Mar 2013
    Posts
    69
    Thanks
    2
    Thanked 11 Times in 8 Posts

    Thumbs up

    Excel stores dates internally as 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:

    PHP Code:
    // 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 timestamp would be:

    Code:
    =(B2-DATE(1970,1,1))*86400
    Now you only need to read the calculated value from the hidden cell.

    Source: blogspot.com

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •