how to convert 9 digit number in SQL database to a date mm/dd/yyyy and time (HH:MM AM/PM)

Posted on 2012-08-14
Last Modified: 2012-08-14
I have a database of records from keys. the tiem and date records are stored in 9-digit values that are all in the future, but, when i pull a report using the software, the same number of records show up with the correct date and time. So I am wondering is someone could help me use excel to convert this:


into something like this:

9/29/2010 10:05:26 AM

Thank you
Question by:speeDemon
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    I do not see the relationship between the two numbers given above. Can you supply a few more coded dates with the corresponding real date/time?
    LVL 1

    Author Comment

    Sure - now based on what I see in the report generated from the software and the database these entries seem to be replated:

    9/29/2010 10:05:28 AM - 465325528
    9/29/2010 10:05:30 AM - 465325530

    here is the email reply i just got from the support email address
    "That value represents the number of seconds since 1996 (January 1st) in UTC.  So, 524510194 seconds starting from 1996 would be: 8/14/2012 5:16:34 PM UTC.  Then, you need to adjust for the current timezone.  Pacific Time is currently -7 UTC, which takes you to the date you referenced, 8/14/2012 10:16:34 AM."
    LVL 43

    Accepted Solution

    Try this formula


    where 35065 represents 1996,
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    You will have to format the result in the date/time format
    LVL 23

    Expert Comment

    Here is a function that will achieve this for you
    Function ConvertDateSerial(serialDate As Long) As Date
      ConvertDateSerial = CDate(serialDate / 86400 + 35065)
    End Function

    Open in new window

    where 86400 is seconds per day
    and 35065 is days from 1/1/1900 (excel serial date start) and 1/1/1996


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now