none
Outlook VBA Show time in miliseconds from 1-1-1970 RRS feed

  • Question

  • Hi Folks,

    For a Outlook App I'm building I need to compare a date in miliseconds that is comming from the database with the current date and time

    I need to show the current time in miliseconds since 1970-1-1.

    Could someone help me out here? I almost killed myself searching for it on google...

    Tuesday, October 23, 2012 9:10 AM

Answers

  • Try this:

        Dim t As Double
        t = ((Date - #1/1/1970#) * 86400 + Timer) * 1000

    Comments:

    Date returns the current date; Date - #1/1/1970# returns the number of days since 01/01/1970.

    We multiply this with 86400, the number of seconds in 1 day.

    Timer returns the number of seconds since midnight today. We add this, then multiply the sum by 1000 to get milliseconds.

    Keep in mind that the minimum time interval in Windows is 1/60 second or ~17 milliseconds, so the above code won't be accurate to the millisecond.


    Regards, Hans Vogelaar

    Tuesday, October 23, 2012 9:50 AM
  • The maximum value for a variable of type Long is 2,147,483,647. The number of milliseconds since 1/1/1970 is much larger.

    Try declaring milliEndTime and millliTimeDiff as Double instead of as Long.


    Regards, Hans Vogelaar

    Tuesday, October 23, 2012 11:22 AM

All replies

  • Try this:

        Dim t As Double
        t = ((Date - #1/1/1970#) * 86400 + Timer) * 1000

    Comments:

    Date returns the current date; Date - #1/1/1970# returns the number of days since 01/01/1970.

    We multiply this with 86400, the number of seconds in 1 day.

    Timer returns the number of seconds since midnight today. We add this, then multiply the sum by 1000 to get milliseconds.

    Keep in mind that the minimum time interval in Windows is 1/60 second or ~17 milliseconds, so the above code won't be accurate to the millisecond.


    Regards, Hans Vogelaar

    Tuesday, October 23, 2012 9:50 AM
  • Dank je wel Hans  :)

    Lets go back to english for the sake of this forum.

    I just found out a solution for my problem in the time inbetween your answer and my appearance on this forum. 

    If I do this, it works : 

    Dim startDate As Date

    startDate = #1/1/1970#

    MsgBox ((1350909660000#) / 1000) - Format(DateDiff("s", startDate, Now())) it works .

    But when i write a function like this one, and call it with the same value as variable i get runtime 6 overflow :( :

    Function Calculate_Task_Delay(milliEndTime As Long)

    Dim startDate As Date

    startDate = #1/1/1970#
       
    Dim milliTimeDiff As Long

    Set milliTimeDiff = ((milliEndTime) / 1000) - Format(DateDiff("s", startDate, Now()))

    MsgBox (milliTimeDiff)

    End Function

    Tuesday, October 23, 2012 11:15 AM
  • The maximum value for a variable of type Long is 2,147,483,647. The number of milliseconds since 1/1/1970 is much larger.

    Try declaring milliEndTime and millliTimeDiff as Double instead of as Long.


    Regards, Hans Vogelaar

    Tuesday, October 23, 2012 11:22 AM