+ Reply to Thread
Results 1 to 5 of 5

Get date as a number in seconds

  1. #1
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Get date as a number in seconds

    Hi guys,

    I've got an url that I pull data from - it has the date as follows:

    "startDateText=2015-02-01&endDateText=2015-03-31"

    it then auto-redirects to an url I capture, which I'll use to pull my data from.
    In this URL, the date is written as:
    "startDate=1422774000&endDate=1427785200"

    Now the difference between these is 5011200, which translates into 58 days in seconds (1 day in seconds = 24 * 3600 = 86400; 5011200 / 86400 = 58).
    This is the number of days between these two dates.
    However, I want to be flexible with my dates.

    So question is - how can I write *any* date in seconds as a full integer?
    example : 2015-02-01 --> 1422774000
    example : 2015-03-31 --> 1427785200

    What calculation to do if I'd want the number for start-date 2014-12-14 or 2013-05-28 etc

    Thanks!

    Jasper

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,453

    Re: Get date as a number in seconds

    What do you mean exactly? The number of seconds since 01/01/1900 ? The number of seconds since the January 1st ?

  3. #3
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Get date as a number in seconds

    Hi Pepe,

    thanks for your quick reply!
    I actually don't know to be honest.

    Basically what I'm asking is:

    What's the rationale behind " 2015-02-01" = "1422774000"
    And how can I use that rationale to calculate historical dates and future dates as such a number?

    Thanks!

  4. #4
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Get date as a number in seconds

    When do you run this file... does it change based on the current date/time of run and/or do you select from "2014-02-01" 12:00 AM? Can you produce several examples with the corresponding date you run it, time you run it as well as the selected date for the run Start/End -

    With that we may be able to build a pattern that would identify what is creating such a number... at a glance I am not catching it
    -If you think you are done, Start over - ELeGault

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Get date as a number in seconds

    That looks like "Unix time" Which is the number of seconds from 1st jan 1970 (usually GMT). To get that value try this formula, assuming date in A2

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

    That gets the number of days since 1/1/1970 and multiplies by the number of seconds in a day - format result cell as number with no decimal places.

    You may need to make a small adjustment for time zones and/or other discrepancies
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 04-30-2014, 12:42 PM
  2. Replies: 1
    Last Post: 11-02-2011, 06:50 PM
  3. Replies: 1
    Last Post: 11-02-2011, 06:44 PM
  4. Replies: 4
    Last Post: 01-18-2010, 04:29 AM
  5. How do i convert a number of seconds to a date/time?
    By Margo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2005, 09:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1