Calculate a grand total time from a txt control calculation

[Guest]

I have a txt box with a control calculating time differences. I would like to
take the times form all of the records in that txt box and add them for a
grand total I can place in my form footer. Any suggestions on how to add time
like that??

 

[Marshall Barton]

I have a txt box with a control calculating time differences. I would like to
take the times form all of the records in that txt box and add them for a
grand total I can place in my form footer. Any suggestions on how to add time
like that??

Use a text box (in the form footer) with an expression like:
=Sum(DateDiff("n", StartTime, EndTime)

That will display the total number of minutes. If you need
something else, please explain.

 

[Guest]

That is a step in the right direction, thanks. I have a "StartTime" and
"EndTime". I have created a text box in my form that calculates the
difference between the start and end time(labblelled Total Time). Now at the
bottom of the form I would like another text box that adds all of the (Total
Time) text boxes all together to create one grant working total.(continuious
form) Thanks for any help you can offer!

 

[Marshall Barton]

You really should provide some additional information, not
just a repeat of the same thing you said earlier.

The text box in the detail section that calculates the total
time for a single record is irrelevant as far as the grand
total is concerned. It can not be used to calculate the
total. The form footer grand total text box really needs to
use the Sum function without referring to any controls on
the form, only fields in the form's record source
table/query.

The expression I posted earlier should do what you asked
(maybe that's not what you want??). If you need more help,
please explain what's wrong with the result (e.g. you want
it in hours:minutes or what??).

 

[Guest]

Sorry for not being so clear. Calculations is new to me.

All I need then is a text box in my form footer that will Sum all the
"StartTime" in each reacord. Sum all the "EndTime" in each record then find
the difference bewteen the two.

I tried the formula you gave:

=Sum(DateDiff("n",[Start Time],[End Time]))

and it gave me a Zero hrs and min Total for some reason. I appreciate your
help! Thanks.

You really should provide some additional information, not
just a repeat of the same thing you said earlier.

The text box in the detail section that calculates the total
time for a single record is irrelevant as far as the grand
total is concerned. It can not be used to calculate the
total. The form footer grand total text box really needs to
use the Sum function without referring to any controls on
the form, only fields in the form's record source
table/query.

The expression I posted earlier should do what you asked
(maybe that's not what you want??). If you need more help,
please explain what's wrong with the result (e.g. you want
it in hours:minutes or what??).
--
Marsh
MVP [MS Access]

That is a step in the right direction, thanks. I have a "StartTime" and
"EndTime". I have created a text box in my form that calculates the
difference between the start and end time(labblelled Total Time). Now at the
bottom of the form I would like another text box that adds all of the (Total
Time) text boxes all together to create one grant working total.(continuious
form) Thanks for any help you can offer!

 

[Guest]

Just an extra note: I do want this calulation to add up past 24 hrs. I am
looking for a running total of hours worked. Thanks in advance.

Sorry for not being so clear. Calculations is new to me.

All I need then is a text box in my form footer that will Sum all the
"StartTime" in each reacord. Sum all the "EndTime" in each record then find
the difference bewteen the two.

I tried the formula you gave:

=Sum(DateDiff("n",[Start Time],[End Time]))

and it gave me a Zero hrs and min Total for some reason. I appreciate your
help! Thanks.

You really should provide some additional information, not
just a repeat of the same thing you said earlier.

The text box in the detail section that calculates the total
time for a single record is irrelevant as far as the grand
total is concerned. It can not be used to calculate the
total. The form footer grand total text box really needs to
use the Sum function without referring to any controls on
the form, only fields in the form's record source
table/query.

The expression I posted earlier should do what you asked
(maybe that's not what you want??). If you need more help,
please explain what's wrong with the result (e.g. you want
it in hours:minutes or what??).
--
Marsh
MVP [MS Access]

That is a step in the right direction, thanks. I have a "StartTime" and
"EndTime". I have created a text box in my form that calculates the
difference between the start and end time(labblelled Total Time). Now at the
bottom of the form I would like another text box that adds all of the (Total
Time) text boxes all together to create one grant working total.(continuious
form) Thanks for any help you can offer!


Roby wrote:
I have a txt box with a control calculating time differences. I would like to
take the times form all of the records in that txt box and add them for a
grand total I can place in my form footer. Any suggestions on how to add time
like that??


:
Use a text box (in the form footer) with an expression like:
=Sum(DateDiff("n", StartTime, EndTime)

That will display the total number of minutes. If you need
something else, please explain.

 

[Guest]

Ok it to calculate the time between.

But The Text box that I have for Grand Total Worked (in the form footer) has
only calculated one record at a time, not all of the records together. this
is really what I am looking for.

Just an extra note: I do want this calulation to add up past 24 hrs. I am
looking for a running total of hours worked. Thanks in advance.

Sorry for not being so clear. Calculations is new to me.

All I need then is a text box in my form footer that will Sum all the
"StartTime" in each reacord. Sum all the "EndTime" in each record then find
the difference bewteen the two.

I tried the formula you gave:

=Sum(DateDiff("n",[Start Time],[End Time]))

and it gave me a Zero hrs and min Total for some reason. I appreciate your
help! Thanks.

You really should provide some additional information, not
just a repeat of the same thing you said earlier.

The text box in the detail section that calculates the total
time for a single record is irrelevant as far as the grand
total is concerned. It can not be used to calculate the
total. The form footer grand total text box really needs to
use the Sum function without referring to any controls on
the form, only fields in the form's record source
table/query.

The expression I posted earlier should do what you asked
(maybe that's not what you want??). If you need more help,
please explain what's wrong with the result (e.g. you want
it in hours:minutes or what??).
--
Marsh
MVP [MS Access]


Roby wrote:
That is a step in the right direction, thanks. I have a "StartTime" and
"EndTime". I have created a text box in my form that calculates the
difference between the start and end time(labblelled Total Time). Now at the
bottom of the form I would like another text box that adds all of the (Total
Time) text boxes all together to create one grant working total.(continuious
form) Thanks for any help you can offer!


Roby wrote:
I have a txt box with a control calculating time differences. I would like to
take the times form all of the records in that txt box and add them for a
grand total I can place in my form footer. Any suggestions on how to add time
like that??


:
Use a text box (in the form footer) with an expression like:
=Sum(DateDiff("n", StartTime, EndTime)

That will display the total number of minutes. If you need
something else, please explain.

 

[Marshall Barton]

Ok it to calculate the time between.

But The Text box that I have for Grand Total Worked (in the form footer) has
only calculated one record at a time, not all of the records together. this
is really what I am looking for.

I'm afraid I do not understand what you are asking here.
Maybe it would help if you provided a simple example of how
you want the form to look.

You said something about a running sum earlier. If that's
really necessary, which I doubt, be aware that it involves a
whole 'nother level of complexities that have to done in the
form's record source query. (Side note: running sums in a
report is very easy.)

 

[Marshall Barton]

You need to think this trough more clearly. It makes no
sense to sum start times (or end) times, what does 9am +
noon + 2pm mean???

On the other hand summing durations like 2.5 hours + 3.75
hours + 9 hours is a normal thing to do. Since (I assume)
you want to include partial hours, the calculations must be
done using minutes, which is what the DateDiff I suggested
takes care of for you.

To format the resulting number of minutes as hours:minutes,
you can do an additional simple calculation. Change the
text box expression to:

=Sum(DateDiff("n", StartTime, EndTime) \ 60 &
Format(Sum(DateDiff("n", StartTime, EndTime) Mod 60, "\:00")
--
Marsh
MVP [MS Access]

Sorry for not being so clear. Calculations is new to me.

All I need then is a text box in my form footer that will Sum all the
"StartTime" in each reacord. Sum all the "EndTime" in each record then find
the difference bewteen the two.

I tried the formula you gave:

=Sum(DateDiff("n",[Start Time],[End Time]))

and it gave me a Zero hrs and min Total for some reason. I appreciate your
help! Thanks.

You really should provide some additional information, not
just a repeat of the same thing you said earlier.

The text box in the detail section that calculates the total
time for a single record is irrelevant as far as the grand
total is concerned. It can not be used to calculate the
total. The form footer grand total text box really needs to
use the Sum function without referring to any controls on
the form, only fields in the form's record source
table/query.

The expression I posted earlier should do what you asked
(maybe that's not what you want??). If you need more help,
please explain what's wrong with the result (e.g. you want
it in hours:minutes or what??).

 

[Guest]

This formula still only calculates one record at a time. I need it to total
all of the records.

I have "StartTime"
I have "EndTime"
I have text box Time Worked =Format([Start Time]-1-[End Time],"Short Time")
which is correct

I have text box Total Time Worked which I need to be a running total of
TimeWorked. right now all I can get it to do is calculate one record at a
time. It won't calculate all of them together.

You need to think this trough more clearly. It makes no
sense to sum start times (or end) times, what does 9am +
noon + 2pm mean???

On the other hand summing durations like 2.5 hours + 3.75
hours + 9 hours is a normal thing to do. Since (I assume)
you want to include partial hours, the calculations must be
done using minutes, which is what the DateDiff I suggested
takes care of for you.

To format the resulting number of minutes as hours:minutes,
you can do an additional simple calculation. Change the
text box expression to:

=Sum(DateDiff("n", StartTime, EndTime) \ 60 &
Format(Sum(DateDiff("n", StartTime, EndTime) Mod 60, "\:00")
--
Marsh
MVP [MS Access]

Sorry for not being so clear. Calculations is new to me.

All I need then is a text box in my form footer that will Sum all the
"StartTime" in each reacord. Sum all the "EndTime" in each record then find
the difference bewteen the two.

I tried the formula you gave:

=Sum(DateDiff("n",[Start Time],[End Time]))

and it gave me a Zero hrs and min Total for some reason. I appreciate your
help! Thanks.

You really should provide some additional information, not
just a repeat of the same thing you said earlier.

The text box in the detail section that calculates the total
time for a single record is irrelevant as far as the grand
total is concerned. It can not be used to calculate the
total. The form footer grand total text box really needs to
use the Sum function without referring to any controls on
the form, only fields in the form's record source
table/query.

The expression I posted earlier should do what you asked
(maybe that's not what you want??). If you need more help,
please explain what's wrong with the result (e.g. you want
it in hours:minutes or what??).


Roby wrote:
That is a step in the right direction, thanks. I have a "StartTime" and
"EndTime". I have created a text box in my form that calculates the
difference between the start and end time(labblelled Total Time). Now at the
bottom of the form I would like another text box that adds all of the (Total
Time) text boxes all together to create one grant working total.(continuious
form) Thanks for any help you can offer!


Roby wrote:
I have a txt box with a control calculating time differences. I would like to
take the times form all of the records in that txt box and add them for a
grand total I can place in my form footer. Any suggestions on how to add time
like that??


:
Use a text box (in the form footer) with an expression like:
=Sum(DateDiff("n", StartTime, EndTime)

That will display the total number of minutes. If you need
something else, please explain.

 

[Marshall Barton]

The formula =Format([Start Time]-1-[End Time],"Short Time")
is NOT correct! If you try that for several different
situations, you will see that it does not deal will all
cases. As I have tried to say several times, the reliable
way to calculate a date/time difference is to use the
DateDiff function using minutes as the units of the
difference.

Important note: Because of the crossing midnight issue, no
single expression can work in all cases unless the start and
end times include the date part. If you do not have the
date part in the times, this whole exercise will be futile.

If you have included the date part in the times or if the
times never, ever span midnight, then the detail section
text box should use the expression:

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

and the form footer section's text box needs to use the
expression:

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

Get that part to work before you go off on a tangent about
the running total, which, as I tried to explain in another
reply, is a completely different problem in a form.
Personally, I think you should scrap the idea of a running
total until you get around to printing a report with this
data.
--
Marsh
MVP [MS Access]

This formula still only calculates one record at a time. I need it to total
all of the records.

I have "StartTime"
I have "EndTime"
I have text box Time Worked =Format([Start Time]-1-[End Time],"Short Time")
which is correct

I have text box Total Time Worked which I need to be a running total of
TimeWorked. right now all I can get it to do is calculate one record at a
time. It won't calculate all of them together.

You need to think this trough more clearly. It makes no
sense to sum start times (or end) times, what does 9am +
noon + 2pm mean???

On the other hand summing durations like 2.5 hours + 3.75
hours + 9 hours is a normal thing to do. Since (I assume)
you want to include partial hours, the calculations must be
done using minutes, which is what the DateDiff I suggested
takes care of for you.

To format the resulting number of minutes as hours:minutes,
you can do an additional simple calculation. Change the
text box expression to:

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

Sorry for not being so clear. Calculations is new to me.

All I need then is a text box in my form footer that will Sum all the
"StartTime" in each reacord. Sum all the "EndTime" in each record then find
the difference bewteen the two.

I tried the formula you gave:

=Sum(DateDiff("n",[Start Time],[End Time]))

and it gave me a Zero hrs and min Total for some reason. I appreciate your
help! Thanks.

:

You really should provide some additional information, not
just a repeat of the same thing you said earlier.

The text box in the detail section that calculates the total
time for a single record is irrelevant as far as the grand
total is concerned. It can not be used to calculate the
total. The form footer grand total text box really needs to
use the Sum function without referring to any controls on
the form, only fields in the form's record source
table/query.

The expression I posted earlier should do what you asked
(maybe that's not what you want??). If you need more help,
please explain what's wrong with the result (e.g. you want
it in hours:minutes or what??).


Roby wrote:
That is a step in the right direction, thanks. I have a "StartTime" and
"EndTime". I have created a text box in my form that calculates the
difference between the start and end time(labblelled Total Time). Now at the
bottom of the form I would like another text box that adds all of the (Total
Time) text boxes all together to create one grant working total.(continuious
form) Thanks for any help you can offer!


Roby wrote:
I have a txt box with a control calculating time differences. I would like to
take the times form all of the records in that txt box and add them for a
grand total I can place in my form footer. Any suggestions on how to add time
like that??


:
Use a text box (in the form footer) with an expression like:
=Sum(DateDiff("n", StartTime, EndTime)

That will display the total number of minutes. If you need
something else, please explain.