+ Reply to Thread
Results 1 to 10 of 10

convert 16 digit timestamp to excel date time

  1. #1
    Registered User
    Join Date
    02-07-2018
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    convert 16 digit timestamp to excel date time

    Trying to get a 16 digit timestamp converted to excel date/time:

    Sample timestamp is as follows:

    1296308149533530


    Thank you for any advice.

  2. #2
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: convert 16 digit timestamp to excel date time

    What are you expecting it to be?

  3. #3
    Registered User
    Join Date
    02-07-2018
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Re: convert 16 digit timestamp to excel date time

    mm/dd/yyyy hh:mm:ss is what I'm hoping for.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: convert 16 digit timestamp to excel date time

    Your solution has 14 digits. What would the example you presented end up being
    1296308149533530
    IF we're looking in order, then 12 is the month and 96 is the day!
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    02-07-2018
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Re: convert 16 digit timestamp to excel date time

    For that particular stamp the date should be 2-1-2021

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: convert 16 digit timestamp to excel date time

    What I'm trying to get at here is..
    How does 1296308149533530 convert to 2-1-2021 (and what happened to hh:mm:ss)?

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,826

    Re: convert 16 digit timestamp to excel date time

    You've got to give us some "meaning" to that time stamp. It does not seem to be a UTC or TAI time (usually some kind of "seconds/milliseconds/similar" since 1/1/1970). Feb 1 2021 would be something on the order of 1.612E9 seconds since 1/1/1970.

    A 15 or so digit integer, I would guess, would be micro/nano seconds since some reference date/time. As a wild guess, I note that that many microseconds since Jan 1 1980 is jan 28 2021. Beyond that, I haven't got much to offer.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,410

    Re: convert 16 digit timestamp to excel date time

    Besides answering the questions posed in the last two messages above, please tell us if your expected date of 2-1-2021 is February 1st or January 2nd.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: convert 16 digit timestamp to excel date time

    With
    1296308149533530

    with
    2-January-2021 = 44198
    or
    1-Feb-2021 = 44228

    could not find any clue that helps
    Quang PT

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: convert 16 digit timestamp to excel date time

    I played with this a little bit but hard to do with a single data point. Since clfletcher is from USA, I assume we are talking about Feb 1, 2021.
    To get the number into "Date" range, assuming a simple conversion, it has be be in microseconds
    =INT(1206308119533530/(24*60*60*1,000,000)) gives 13961 or 22 Mar 1938.
    To normalize that to Feb 1, 2021, one would have to add 30267 (or 12 Nov 1982)
    Doesn't really make sense unless that's based on some software start date. Again, a second data point would help.

+ 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: 7
    Last Post: 07-11-2019, 11:27 AM
  2. Replies: 2
    Last Post: 06-09-2018, 11:21 AM
  3. [SOLVED] Excel VBA: Convert a date string to a HFS + timestamp
    By terriertrip in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2016, 03:21 PM
  4. Convert 3 digit or 4 digit number to time format
    By dubcap01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2015, 04:30 AM
  5. Convert Timestamp to date.
    By brent_milne in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2015, 01:31 PM
  6. [SOLVED] VBA - Convert Date/Timestamp into American Date and Military Timestamp
    By ryanmorris in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-01-2015, 03:43 PM
  7. [SOLVED] Convert Unix timestamp to Readable Date/time
    By Emily in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2006, 08:00 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1