Magento Expert Forum - Improve your Magento experience

Results 1 to 4 of 4

Convert Excel dates into PHP timestamps

  1. #1
    Junior Member ccvv's Avatar
    Join Date
    Mar 2013
    Posts
    64
    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. Everythings one day off? I know it. Heres 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 Excels 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 wasnt, thus there is an extra day which must be accounted for in PHP (and the rest of the world). Subtracting 1 from Excels number will fix this problem.

    View more threads in the same category:


  2. #2
    Junior Member jaredovi's Avatar
    Join Date
    Mar 2013
    Posts
    70
    Thanks
    2
    Thanked 12 Times in 9 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

  3. #3
    Junior Member
    Join Date
    Sep 2018
    Location
    United Kingdom
    Posts
    97
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    40703 is the quantity of entire days since 01/01/1900 and 0.4375 speaks to the time (10.5/24 = 0.4375). When you process dates read from an Excel spreadsheet (for example utilizing PHPExcel) you regularly need to change over them into a UNIX timestamp for example various seconds passed since midnight of January 1, 1970 UTC.

  4. #4
    Junior Member
    Join Date
    Sep 2018
    Location
    United Kingdom
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    PHP program to set a date time value in excel sheet. require_once .Set the number format mask so that the excel timestamp. Convert to an Excel date/time.

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
  •