Calculate Time in A Report

  • Thread starter Thread starter PolQueen
  • Start date Start date
P

PolQueen

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.
 
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
 
Thank you, John.

When I try to use:
Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")

I get this message: Syntax error in query expression
'First([Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")])'

Should I be doing something else?

Thank you.



John Spencer said:
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
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.
Click to expand...
.
Click to expand...
 
Where did FIRST come from? As far as I know you cannot combine aggregate
functions in one query.

If you are doing this in query design view, try using EXPRESSION as the choice
in the Total box under the expression above.

Otherwise, post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thank you, John.

When I try to use:
Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")

I get this message: Syntax error in query expression
'First([Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")])'

Should I be doing something else?

Thank you.



John Spencer said:
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
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.
Click to expand...
.
Click to expand...
Click to expand...
 
John,

I have no idea where the "FIRST" came from - it was in the pop up syntax
error message box.

I used your formula exactly (adding an = sign in front when pasting it into
the control source field). I have never done a formula like that with the
format in it – is that the problem? Should I be doing something else? Here
is what the fields look like:

CONTROL SOURCE =Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")

INPUT MASK 00:00;0;_

FORMAT Fixed, DECIMALS 2

As for using the query to do this, I don’t know SQL that well, and since I
am summing by group and then the master report total, I don’t have those
types of fields in the query, so I don’t know how to do that, and then make
them show in the report properly. I have always used regular numbers when
doing this, not time, and have never had this issue before.

Thank you for your help!


John Spencer said:
Where did FIRST come from? As far as I know you cannot combine aggregate
functions in one query.

If you are doing this in query design view, try using EXPRESSION as the choice
in the Total box under the expression above.

Otherwise, post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thank you, John.

When I try to use:
Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")

I get this message: Syntax error in query expression
'First([Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")])'

Should I be doing something else?

Thank you.



John Spencer said:
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.

.
Click to expand...
Click to expand...
.
Click to expand...
 
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 said:
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
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.
Click to expand...
.
Click to expand...
 
Back
Top