Magento Expert Forum - Improve your Magento experience

Results 1 to 6 of 6

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. 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
    68
    Thanks
    2
    Thanked 13 Times in 10 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. The Following User Says Thank You to jaredovi For This Useful Post:

    luisao.ba (13-09-2019)

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

    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.

  5. #4
    Junior Member
    Join Date
    Sep 2018
    Location
    United Kingdom
    Posts
    464
    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.

  6. #5
    New member
    Join Date
    Sep 2019
    Posts
    1
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default It Works! Thank You!

    Thank You!

  7. #6
    Junior Member
    Join Date
    Sep 2018
    Location
    United Kingdom
    Posts
    157
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Default

    Convert Unix dates for export to Excel with PHP or MySQL Unix timestamps are in UTC (GMT), so the first thing we need to do is convert to

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
  •