Grand Total Time

[auujxa2 via AccessMonster.com]

I've exhausted my research ability on this one. Please help!

I have StartTime1, EndTime1, TotalTime1. (through 7 for a full week)

The start and end times are medium time, because my customer doesn't want
military short time. But I have total time at short time, using the datediff,
"n" /1440 function.

And everything so far works great.

But the Grand total line, which sums TotalTime1:TotalTime7 "crashes" after
the sum is more than 24 hours.

Suggestions please!!

 

[Allen Browne]

Common question. See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[Daryl S]

Auujxa2 -

Time formats cannot show more than 24 hours. Format your GrandTotalTimes (I
assume this is on a report) to be either a numeric field or a date/time
field. You can also have hidden fields that contain the TotalTime for each
record in a decimal hours format, and show your grand totals based on that
field instead of the short time field.

 

[auujxa2 via AccessMonster.com]

Thanks Allen. My grand total is giving me a concatenation of all 7 totals.
(exmaple: 6:305:308:459:45)

here is one of 7 totals that are summed.
=DateDiff("n",[txtStartTime1],[txtEndTime1])\60 & Format(DateDiff("n",
[txtStartTime1],[txtEndTime1]) Mod 60,"\:00")

Common question. See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

I've exhausted my research ability on this one. Please help!

[quoted text clipped - 11 lines]

Suggestions please!!

 

[John Spencer]

Well that expression is returning a STRING and when you use + with strings
they get concatenated into a longer string.

You might try adding the number of minutes BEFORE you attempt to create the
formatted string

DateDiff("n",[txtStartTime1],[txtEndTime1]) +
DateDiff("n",[txtStartTime2],[txtEndTime2])

Once you have summed the minutes, you can then apply the formatting if you
need "hours:Minutes". Of course that is going to be a bit on the unwieldy
side. With just two sets, you end up with something that could look like:

(DateDiff("n",[txtStartTime1],[txtEndTime1]) +
DateDiff("n",[txtStartTime2],[txtEndTime2]))\60 &
Format((DateDiff("n",[txtStartTime1],[txtEndTime1]) +
DateDiff("n",[txtStartTime2],[txtEndTime2])) MOD 60, "\:00")

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

Thanks Allen. My grand total is giving me a concatenation of all 7 totals.
(exmaple: 6:305:308:459:45)

here is one of 7 totals that are summed.
=DateDiff("n",[txtStartTime1],[txtEndTime1])\60 & Format(DateDiff("n",
[txtStartTime1],[txtEndTime1]) Mod 60,"\:00")

Common question. See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

I've exhausted my research ability on this one. Please help!

[quoted text clipped - 11 lines]

Suggestions please!!

 

[John W. Vinson]

I've exhausted my research ability on this one. Please help!

I have StartTime1, EndTime1, TotalTime1. (through 7 for a full week)

Then you have an incorrectly normalized database. You should have one record
for each day.

The start and end times are medium time, because my customer doesn't want
military short time. But I have total time at short time, using the datediff,
"n" /1440 function.

The format is ABSOLUTELY IRRELEVANT. See below.

And everything so far works great.

But the Grand total line, which sums TotalTime1:TotalTime7 "crashes" after
the sum is more than 24 hours.

Suggestions please!!

A date/time value is stored as a Double Float number, a count of days and
fractions of a day (times) since midnight, December 30, 1899. The format just
controls how that number is displayed, not what's stored; you can have a wide
variety of different formats.

As such, a Date/Time field is best used for storing specific points in time.
1.25 is actually equivalent to #12/31/1899 06:00:00# - not 30:00, and there is
no format that will display it as 30:00.

I would suggest calculating your durations as integer minutes - NOT as
date/time values; you can *display* the duration by using an expression like

Duration \ 60 & ":" & Duration MOD 60, "00")

where duration is an integer number of minutes.

 

[John W. Vinson]

Thanks Allen. My grand total is giving me a concatenation of all 7 totals.
(exmaple: 6:305:308:459:45)

here is one of 7 totals that are summed.
=DateDiff("n",[txtStartTime1],[txtEndTime1])\60 & Format(DateDiff("n",
[txtStartTime1],[txtEndTime1]) Mod 60,"\:00")

If you insist on using seven fields in one record rather than a correctly
normalized one-record-per-day, then add up the times and format the sum:

(DateDiff("n",[txtStartTime1],[txtEndTime1]) +
DateDiff("n",[txtStartTime2],[txtEndTime2]) +
DateDiff("n",[txtStartTime3],[txtEndTime3]) <etc>)
MOD 60 & Format(DateDiff("n",[txtStartTime1],[txtEndTime1]) +
DateDiff("n",[txtStartTime2],[txtEndTime2]) +
DateDiff("n",[txtStartTime3],[txtEndTime3]) <etc>) MOD 60, "\:00")