Formula to calculate time between start time and End time

[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!!

 

[Arvin Meyer [MVP]]

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.

 

[Guest]

Hi Roby

Check out the excelent articale on this site

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

Hope this helps

 

[Guest]

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!!!

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!!

 

[Guest]

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"

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!!!

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!!

 

[Douglas J. Steele]

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)

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"

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!!!

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!!