Seconds & DateDiff

  • Thread starter Thread starter Bruce Rodtnick
  • Start date Start date
B

Bruce Rodtnick

This has to do with TimeCode and the entries come in like:

1:03
4:15
1:25:16

I am trying to get the time difference between two times, say 1:03 (1
minute and 3 seconds) and 1:25 1 minute and 25 seconds. The result
should be :22 (22 seconds) I'm using :

=DateDiff("s",[TC-In],[TC-Out])/86400

and it works fine until I get a time over 24 as in 38:45 (38 minutes 45
seconds). DateDiff is looking at the number as hours and minutes and I
get an error reading (it doesn't understand a time over 24:00). Is
there any way to have the code assume it minutes and seconds that I am
using?

Bruce Rodtnick
 
Is there any way to have the code assume it minutes and seconds that I am
using?
Click to expand...

Unfortunately, no - unlike Excel, Access doesn't have a date/time
format displaying over 24 hours.

I'd suggest not using Date/Time values AT ALL - instead use an
expression like

DateDiff("s",[TC-In],[TC-Out]) \ 3600 &
Format(DateDiff("s",[TC-In],[TC-Out]) \ 60 MOD 60, ":00") &
Format(DateDiff("s",[TC-In],[TC-Out]) MOD 60, ":00")

The \ is an integer divide, and MOD gives the remainder after
division.
 
The field is a text field with an Input Mask as !##\:##\:##;; It puts the
numbers in from the left and if there are only three numbers (318) it only puts
in 3:18 and doesn't try to put the entire 6 digits in there. It would like to
subtract the first one from the second one to get a total in minutes and
seconds. DateDiff I'm sure assumes it is a clock time and 3:18 as in 3:18 AM.

If I subtract 318 (3:18) from 325 (3:25) I'll get 7 and that can be converted
into 7 seconds (:07), but subtracting 318 (3:18) from 525 (5:25) I'll get 207,
but the number of seconds would be 127 or 2:07. How can I make that work?

B

John said:
Is there any way to have the code assume it minutes and seconds that I am
using?
Click to expand...

Unfortunately, no - unlike Excel, Access doesn't have a date/time
format displaying over 24 hours.

I'd suggest not using Date/Time values AT ALL - instead use an
expression like

DateDiff("s",[TC-In],[TC-Out]) \ 3600 &
Format(DateDiff("s",[TC-In],[TC-Out]) \ 60 MOD 60, ":00") &
Format(DateDiff("s",[TC-In],[TC-Out]) MOD 60, ":00")

The \ is an integer divide, and MOD gives the remainder after
division.
Click to expand...
 
The field is a text field with an Input Mask as !##\:##\:##;; It puts the
numbers in from the left and if there are only three numbers (318) it only puts
in 3:18 and doesn't try to put the entire 6 digits in there. It would like to
subtract the first one from the second one to get a total in minutes and
seconds. DateDiff I'm sure assumes it is a clock time and 3:18 as in 3:18 AM.

If I subtract 318 (3:18) from 325 (3:25) I'll get 7 and that can be converted
into 7 seconds (:07), but subtracting 318 (3:18) from 525 (5:25) I'll get 207,
but the number of seconds would be 127 or 2:07. How can I make that work?
Click to expand...

Well... by not using a Text field, or by using the CDate() function to
convert the text string to date/time and then using DateDiff.

Text strings are NOT numbers, nor are they dates or times, and Access
can't be faulted IMHO for failing to treat them as if they were!
 
Back
Top