Formula to calculate time between start time and End time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a StartTime field and a EndTime Field. I would a field at the bottom
of a report that will calculate the time between the start time and the end
time, then give me a grand total. Here is the formula that I have tried but
don't work.

=TimeValue(Sum([End Time]))-(Sum([Start Time]))

I used the sum function and it worked great. Minus'ed the end time from the
start time and it gave me a correct total. But once you get 2300hrs and then
0300hrs it becomes a minus. Any help would be greatly greatly appreciated!!
 
There are only 24 hours in a day, so if you want to find an interval greater
than that period, you must use a date and a time in your calculations. Then
using the DateDiff() function you can find any interval.
 
Thank you both very much for the reply. I have rearranged to use the DatDiff
functiona dn getting it to work!!! But now Wayne you are right, I have a 24
hr operation adn anything after midnight gives me aminus total. I read that
article you had sent. do I need to write the # into code? A formula in the
query? Control Source? and would it be an extenstion to what I have. this is
what I have:

=DateDiff("h",[Start Time],[End Time]) & ":" & Format((DateDiff("n",[Start
Time],[End Time]) Mod 60),"00")

Your help is much appreciated!!!

Wayne-I-M said:
Hi Roby

Check out the excelent articale on this site

http://www.mvps.org/access/datetime/date0008.htm

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Roby said:
I have a StartTime field and a EndTime Field. I would a field at the bottom
of a report that will calculate the time between the start time and the end
time, then give me a grand total. Here is the formula that I have tried but
don't work.

=TimeValue(Sum([End Time]))-(Sum([Start Time]))

I used the sum function and it worked great. Minus'ed the end time from the
start time and it gave me a correct total. But once you get 2300hrs and then
0300hrs it becomes a minus. Any help would be greatly greatly appreciated!!
Click to expand...
Click to expand...
 
OK I figured it out using this simple formula (this includes midnight and
after time)

=Format([Start Time]-1-[End Time],"Short Time")

Now,lol, 2nd question. Can I signify this field to save information in a
table in a field labblled "Totaltimeonshift"

Roby said:
Thank you both very much for the reply. I have rearranged to use the DatDiff
functiona dn getting it to work!!! But now Wayne you are right, I have a 24
hr operation adn anything after midnight gives me aminus total. I read that
article you had sent. do I need to write the # into code? A formula in the
query? Control Source? and would it be an extenstion to what I have. this is
what I have:

=DateDiff("h",[Start Time],[End Time]) & ":" & Format((DateDiff("n",[Start
Time],[End Time]) Mod 60),"00")

Your help is much appreciated!!!

Wayne-I-M said:
Hi Roby

Check out the excelent articale on this site

http://www.mvps.org/access/datetime/date0008.htm

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Roby said:
I have a StartTime field and a EndTime Field. I would a field at the bottom
of a report that will calculate the time between the start time and the end
time, then give me a grand total. Here is the formula that I have tried but
don't work.

=TimeValue(Sum([End Time]))-(Sum([Start Time]))

I used the sum function and it worked great. Minus'ed the end time from the
start time and it gave me a correct total. But once you get 2300hrs and then
0300hrs it becomes a minus. Any help would be greatly greatly appreciated!!
Click to expand...
Click to expand...
Click to expand...
 
There's no reason to store the calculation.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Roby said:
OK I figured it out using this simple formula (this includes midnight and
after time)

=Format([Start Time]-1-[End Time],"Short Time")

Now,lol, 2nd question. Can I signify this field to save information in a
table in a field labblled "Totaltimeonshift"

Roby said:
Thank you both very much for the reply. I have rearranged to use the
DatDiff
functiona dn getting it to work!!! But now Wayne you are right, I have a
24
hr operation adn anything after midnight gives me aminus total. I read
that
article you had sent. do I need to write the # into code? A formula in
the
query? Control Source? and would it be an extenstion to what I have. this
is
what I have:

=DateDiff("h",[Start Time],[End Time]) & ":" &
Format((DateDiff("n",[Start
Time],[End Time]) Mod 60),"00")

Your help is much appreciated!!!

Wayne-I-M said:
Hi Roby

Check out the excelent articale on this site

http://www.mvps.org/access/datetime/date0008.htm

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a StartTime field and a EndTime Field. I would a field at the
bottom
of a report that will calculate the time between the start time and
the end
time, then give me a grand total. Here is the formula that I have
tried but
don't work.

=TimeValue(Sum([End Time]))-(Sum([Start Time]))

I used the sum function and it worked great. Minus'ed the end time
from the
start time and it gave me a correct total. But once you get 2300hrs
and then
0300hrs it becomes a minus. Any help would be greatly greatly
appreciated!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top