Subtracting dates (1 Viewer)

[radek225]

I would like to Substract one date from another using dao.recodset and get format "dd hh:mm:ss"

My fields in table e.g.
strDate = 17.10.2016 08:17:45
endDate = 18.10.2016 09:23:36

the problem is, if trying rst!endDate - rst!strDate then result is "31 01:05:31" I tried various combination e.g.

Format(rst!endDate - rst!strdate, "DD hh:mm:ss")

Format(CDate(Format(rst!endDate, "yyyy-mm-dd hh:mm:ss")) - CDate(Format(rst!strdate, "yyyy-mm-dd hh:mm:ss")), "dd hh:mm:dd")

And always got wrong value:/

What's wrong in my code? How to get correct result?

 

[jdraw]

Try the DateDiff function

Why are you dates string data type?

 

[radek225]

Try the DateDiff function

Why are you dates string data type?

Interval in Datediff can't be "dd hh:mm:ss"

I converting string to data to correctly substract - is it wrong?

 

[MarkK]

A date is stored as a number. To do math with dates there is no need to Format() them as strings, and then CDate() them back to a dates. This is enough....

rs!endDate - rs!startDate

A date in VBA is always a quantity of time since a fixed benchmark date...

? format(cdate(0), "mmm d, yyyy")
Dec 30, 1899

...so if you do a date subtraction that results in 5.75 days, that is 5.75 days from Dec 30, 1899, like...

? format(cdate(5.75), "mmm d, yyyy  hh:nn:ss")
Jan 4, 1900  18:00:00

...so if you format that as dd hh:nn:ss, you get...

04 18:00:00

One way to get the number of days is to take the integer portion of the subtraction, like...

    Dim diff As Single
    Dim days As Integer
    Dim time As Single
    diff = rs!EndDate - rs!StartDate
    days = Fix(diff)    [COLOR="Green"]'integer portion of date difference = days[/COLOR]
    time = days - diff  [COLOR="Green"]'decimal portion of the date = time[/COLOR]
    Debug.Print days & " " & Format(time, "hh:nn:ss")

See what happens there? We separate the days difference from the date variable because the date variable can only show us a date in respect to the fixed benchmark. Time shows us the time since midnight.

Hope that makes sense.

 

[radek225]

A date is stored as a number. To do math with dates there is no need to Format() them as strings, and then CDate() them back to a dates. This is enough....

rs!endDate - rs!startDate

A date in VBA is always a quantity of time since a fixed benchmark date...

? format(cdate(0), "mmm d, yyyy")
Dec 30, 1899

...so if you do a date subtraction that results in 5.75 days, that is 5.75 days from Dec 30, 1899, like...

? format(cdate(5.75), "mmm d, yyyy  hh:nn:ss")
Jan 4, 1900  18:00:00

...so if you format that as dd hh:nn:ss, you get...

04 18:00:00

One way to get the number of days is to take the integer portion of the subtraction, like...

    Dim diff As Single
    Dim days As Integer
    Dim time As Single
    diff = rs!EndDate - rs!StartDate
    days = Fix(diff)    [COLOR="Green"]'integer portion of date difference = days[/COLOR]
    time = days - diff  [COLOR="Green"]'decimal portion of the date = time[/COLOR]
    Debug.Print days & " " & Format(time, "hh:nn:ss")

See what happens there? We separate the days difference from the date variable because the date variable can only show us a date in respect to the fixed benchmark. Time shows us the time since midnight.

Hope that makes sense.

I even can't imagine better lesson. Thank you very much MarkK

 

[MarkK]

You're welcome radek. Hope you are doing well,