Time Calculation

[Sharon W]

Hello,
This is what I am trying to do:
There are two fields, [Time Call Received] and [Time Call Ended], what I
would like to do is calculate the amount of time it takes to process a call.
The users enter the time as such: 9:30 PM or AM depending upon when the call
is received. I tried the DateDiff function but I run into problems when a
call starts at 11:30 AM and ends at 1:30 PM. Similarly, if a call starts at
11:30 PM and ends at 1:30 AM.

Can anyone assist me with this problem?

 

[John W. Vinson]

Hello,
This is what I am trying to do:
There are two fields, [Time Call Received] and [Time Call Ended], what I
would like to do is calculate the amount of time it takes to process a call.
The users enter the time as such: 9:30 PM or AM depending upon when the call
is received. I tried the DateDiff function but I run into problems when a
call starts at 11:30 AM and ends at 1:30 PM. Similarly, if a call starts at
11:30 PM and ends at 1:30 AM.

Can anyone assist me with this problem?

If your calls run over midnight, then you should certainly store both the date
and the time of the call in one field: e.g. a call might last from #12/29/2008
11:30:00pm# until #12/30/2008 01:30:00am#. DateDiff("n", [starttime],
[endtime]) will correctly give 120 minutes.

If you JUST store the time, Access actually stores it as a time on December
30, 1899 (yes, a bit over 108 years ago). 1:30am on that date was 22 hours
before 11:30pm. You'll need to add 24 hours if the [Time Call Ended] is
earlier than the [Time Call Received]. I take it you aren't expecting to have
calls longer than 24 hours (I sure hope not anyway!!!) but that's another
reason.

Could you explain the nature of the problems with DateDiff over noon? That
should work just fine...

 

[Sharon W]

Hello,
Thank you very much. I did change the format in my table to include date but
when I did the time computation it still didn't work when I went from 9:30
PM, start, to 12:30 AM, end.
I actually didn't have any problem with the noon.

Thank you

Hello,
This is what I am trying to do:
There are two fields, [Time Call Received] and [Time Call Ended], what I
would like to do is calculate the amount of time it takes to process a call.
The users enter the time as such: 9:30 PM or AM depending upon when the call
is received. I tried the DateDiff function but I run into problems when a
call starts at 11:30 AM and ends at 1:30 PM. Similarly, if a call starts at
11:30 PM and ends at 1:30 AM.

Can anyone assist me with this problem?

If your calls run over midnight, then you should certainly store both the date
and the time of the call in one field: e.g. a call might last from #12/29/2008
11:30:00pm# until #12/30/2008 01:30:00am#. DateDiff("n", [starttime],
[endtime]) will correctly give 120 minutes.

If you JUST store the time, Access actually stores it as a time on December
30, 1899 (yes, a bit over 108 years ago). 1:30am on that date was 22 hours
before 11:30pm. You'll need to add 24 hours if the [Time Call Ended] is
earlier than the [Time Call Received]. I take it you aren't expecting to have
calls longer than 24 hours (I sure hope not anyway!!!) but that's another
reason.

Could you explain the nature of the problems with DateDiff over noon? That
should work just fine...

 

[John W. Vinson]

Hello,
Thank you very much. I did change the format in my table to include date but
when I did the time computation it still didn't work when I went from 9:30
PM, start, to 12:30 AM, end.
I actually didn't have any problem with the noon.

The format is *completely irrelevant*. The Format of a date/time field just
controls how the value is displayed, not what's stored.

You can store #12:30:00 am# in a date/time field; if you display that value
with a Long Date format (or any format including both the date and time),
you'll see that it's actually #12/30/1899 00:30:00# - just past midnight on
December 30, 1899. This value is 21 hours before #12/30/1899 21:30:00#, which
is what you get if you put 9:30PM into the field.

Or you can store the date and time together in the same field. #1/19/2009
21:30:00# - 9:30 this evening - is in fact three hours before #1/20/2009
00:30:00#, 12:30 tomorrow morning.

The builtin Time() function gives you just a pure time value (on December 30,
1899). The Now() function gives the current date and time in the same field.
Both have their uses.