Date and Time difference

[Smart]

I have four fields

Date Sent (date) Time Sent (Time) Date Recvd (date) Time Rcvd (time)

I want to know how many hours have elapsed between date and time sent and date and time rcvd


Any Ideas please as I am having a blonde moment

 

[Brianwarnock]

Datediff("h",date1,date2)

Brian

Edit Just noticed that you have separate fields for date and time so some further arithmetic will be needed to sum both, hum the sent time could be less than the rcved time, not sure now.

 

[Brianwarnock]

Ok quick test shows not a problem

Datediff("h",datesent,datercvd)+Datediff("h",timesent,timercvd)

Brian

 

[namliam]

Well allmost I think Brian...

datediff("H", [Date Sent]+[Time Sent], [Date Recvd]+[Time Rcvd])

Edit:
Just want to add that one should not use spaces or special characters in colum and/or table names.

 

[Brianwarnock]

Yes both syntax work, agree about spaces to the extent that give or take a typo I omitted them

Brian

 

[Jon K]

It depends on the results you want and the length of elapsed time.


DatePart("h",([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent]))
- returns an integer between 0 and 23, representing the number of completed hours. It works correctly when elapsed time is less than 24 hours.

(([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent]))*24
- returns elapsed time in numeric hours with several decimal places. It always works correctly.

Format((([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent])),"hh:nn:ss")
- returns elapsed time in Hours:Minutes:Seconds in a text string. It works correctly when elapsed time is less than 24 hours.

Int((([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent]))*24) & ":" & Format((([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent])),"nn:ss")
- returns elapsed time in Hours:Minutes:Seconds in a text string. It works if elapsed time may be 24 hours or over.
.

 

[EMP]

DateDiff("h") doesn't work correctly all the time.

For example, DateDiff("h") of 1/10/2007 3:59:59 AM and 1/10/2007 4:00:00 AM returns 1 hour though actually only one second has elapsed.

^

 

[boblarson]

EMP is correct and that's why for time differences I will usually go with minutes or seconds and then do calculations for hours. The same actually goes for years too.

 

[namliam]

DateDiff("h") doesn't work correctly all the time.

For example, DateDiff("h") of 1/10/2007 3:59:59 AM and 1/10/2007 4:00:00 AM returns 1 hour though actually only one second has elapsed.

^

This seems only logical as you are asking the difference in hours. This rounds UP allways...

DatePart("h",([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent]))
- returns an integer between 0 and 23, representing the number of completed hours. It works correctly when elapsed time is less than 24 hours.

Sorry not true...
x = now()
y = now + 1.1
?datediff("H",x,y)
26
?datediff("H",y,x)
-26

Int((([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent]))*24) & ":" & Format((([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent])),"nn:ss")

This one offcourse is best but this the OP didnt ask....

 

[EMP]

Originally Posted by namliam

Sorry not true...
x = now()
y = now + 1.1
?datediff("H",x,y)
26
?datediff("H",y,x)
-26

I think you need to re-read Jon's statement:-

DatePart("h",([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent]))
- returns an integer between 0 and 23, representing the number of completed hours. It works correctly when elapsed time is less than 24 hours.


Jon was talking about DatePart, not DateDiff.
And when 1.1 was added to Now(), the time elapsed was over 24 hours.

I think when Jon said: "It depends on the results you want and the length of elapsed time.", he was referring to the OP's question.

Originally Posted by namliam

This seems only logical as you are asking the difference in hours. This rounds UP allways...

As for the logic of rounding UP 1 second to 1 hour, I have no comment.

But if you try DateDiff("H") on 1/10/2007 3:00:00 AM and 1/10/2007 4:30:00 AM, you will see that it rounds DOWN, not UP.

^

 

[Brianwarnock]

DateDiff("h") doesn't work correctly all the time.

For example, DateDiff("h") of 1/10/2007 3:59:59 AM and 1/10/2007 4:00:00 AM returns 1 hour though actually only one second has elapsed.

^

Been out of the loop too long, forgot about datediffs penchant for rounding up, and my test was too simplistic to catch it. apologies to Smart

Brian

 

[Jon K]

My two cents

DateDiff never rounds. It strips except for DateDiff("s").

So DateDiff("n") strips the seconds.
DateDiff("h") strips the minutes and seconds.
DateDiff("d") strips the hours, minutes and seconds.
etc.
.

 

[Brianwarnock]

The important point is that it strips before the subtraction takes place, otherwise it would be the eqivalent to rounding down, doing it the way it does is equivalent to rounding up.

Just my 2 pence worth.

Brian

 

[The_Doc_Man]

If date/time received and date/time sent were one field, you could just subtract the fields and get the difference in days and fractions thereof. OK, if you wanted something fancy for the difference, you might have to work on a formatter or something like that.

If you have date and time in separate fields, you are asking for a harder time than you need. Rethink combining them.

 

[namliam]

I agree combining the fields saves space (not much of an issue ussualy) but makes for easier access to your data. Unless there is a major requirement for this... I too would join the 2 fields.