Prev: Line Break in text field in a form
Next: Multiple detail/record per break to reduce pages
From: Duane Hookom on 23 Mar 2010 17:00
It looks like there are some missing )s after [EndTime]:

Sum(DateDiff("n",[StartTime],[EndTime])) gives you the number of minutes

Sum(DateDiff("n",[StartTime],[EndTime]))\60 gives you the number of hours
(that
is integer division so it removes the fractional portion).

--
Duane Hookom
Microsoft Access MVP


"John Spencer" wrote:

> Sum(DateDiff("n",[StartTime],[EndTime]) gives you the number of minutes
>
> Sum(DateDiff("n",[StartTime],[EndTime])\60 gives you the number of hours (that
> is integer division so it removes the fractional portion).
>
> Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60 gives you the number of
> leftover minutes.
>
> So for display purposes in hours and minutes:
> Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
> Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")
>
> 27.08 is 27 hours and 5 minutes (actually the number is probably
> 27.08333333333 but your 2 decimal places is rounding that off).
>
> Your problem with the first solution is you are attempting to show a duration
> of time using a point in time. 27 hours and 5 minutes. A point in time is
> never going to exceed 24 hours. Once it does, you are going to end up with a
> day (date) plus the time left over after you divide by 24 hours.
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> PolQueen wrote:
> > I know that working with time in Access can be tricky. I have multiple
> > locations that I am tracking times that vehicles are covering for another
> > vehicle, and I really need to use time format. When I have only a few
> > entries, the first formula works with no problem, but with a lot of entries,
> > it is incorrect:
> >
> > =Sum([StartTime]-[ EndTime])
> > Format: Short Time
> > Input Mask: 00:00;0;_
> > (25 entries ranging from :45 minutes to 1:20 minutes = 3:05 hours, which is
> > wrong.) When I have a lot of entries (some are in the hundreds), the above
> > formula is completely wrong.
> >
> > I can get close to the correct number with the formula shown below, but it
> > is no longer in time format and I am questioned why something is out of
> > service for 27.08 hours when everything is always in five minute increments.
> >
> > =Sum(DateDiff("n",[StartTime],[EndTime])/60)
> > Format: Fixed
> > Decimal Places: 2
> > (The same 25 entries ranging from :45 minutes to 1:20 minutes = 27:08 hours,
> > is actually 27:05 hours).
> >
> > What is the proper way to calculate this so that I get the true time?
> >
> > Thank you.
> >
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Line Break in text field in a form
Next: Multiple detail/record per break to reduce pages