Seconds & DateDiff

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
 
J

John Vinson

Is there any way to have the code assume it minutes and seconds that I am
using?

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.
 
B

Bruce Rodtnick

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?

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.
 
J

John Vinson

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?

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top