How would one Sum Hours and Minuites

[Roby]

I am looking to sum one text box containing hh:mm in a continuios report to
be calculated in the report footer. Here is the formula I have used that does
not work.
=Sum("n", [TotalTime])\60

 

[Stefan Hoffmann]

hi Roby,

I am looking to sum one text box containing hh:mm in a continuios report to
be calculated in the report footer. Here is the formula I have used that does
not work.
=Sum("n", [TotalTime])\60

http://www.mvps.org/access/datetime/index.html


mfG
--> stefan <--

 

[Roby]

I found in that article what I needed but I am not sure how to use it. I have
a text box with hours and minuites in it.[TotalTime] I just need to add up
all of the [TotalTime] throught a continuios form/report. I am stuck I gues
getting the hours and minuites formatted to intergers so I can add them.
Foudn this but don't knowhow to use it.

minutes =mins/(24*60)

Hours=hrs/24

time=hours + minutes

?#07:30#-#00:00#
0.3125
minutes=30/(24*60)
hours=7/24
?hours+minutes
0.3125

hi Roby,

I am looking to sum one text box containing hh:mm in a continuios report to
be calculated in the report footer. Here is the formula I have used that does
not work.
=Sum("n", [TotalTime])\60

http://www.mvps.org/access/datetime/index.html


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Roby,

I found in that article what I needed but I am not sure how to use it. I have
a text box with hours and minuites in it.[TotalTime] I just need to add up
all of the [TotalTime] throught a continuios form/report.

[TotalTime] is date/time field?

Use in a hidden field, i call it hiddenSum:

=Sum([TotalTime])

Then you can use the DatePart function to format your display field:

DatePart("d", hiddenSum) * 24 + _
DatePart("h", hiddenSum) & ":" & _
DatePart("m", hiddenSum)


mfG
--> stefan <--

 

[John Spencer]

Assumptions:
TotalTime is a DateTime field that contains only a time component.

Sum(CDbl(Nz([TotalTime],0)) should give you a number that represents the
days and partial days (hours and minutes)

Then you should be able to convert that to hours and minutes using some
math.

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

The problem results from storing a duration of time in a field that is meant
to contain a point in time.
I worked 8 hours and 15 minutes is a duration, I worked from 8:15AM is a
point in time.
The first is better stored as 495 (8*60 +15) minutes.

Access stores points in time in a DateTime field using a floating point
number that represents the value as on offset from midnight of Dec 30, 1899.
The integer portion represents the number of days and the portion after the
decimal point represents the time as a fraction of the day.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Roby]

I truly appreciate the help, and patience. With the information provided
where do I put

Sum(CDbl(Nz([TotalTime],0)) (does this go in the text box?)

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

(also where does the above formula go to make this possbile?)

I just want to add all the hours and minuites in a text box in a footer.
Your asumptions where correct in that I am summing a text box only contianing
hh:mm.
Thank you in advance!

Assumptions:
TotalTime is a DateTime field that contains only a time component.

Sum(CDbl(Nz([TotalTime],0)) should give you a number that represents the
days and partial days (hours and minutes)

Then you should be able to convert that to hours and minutes using some
math.

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

The problem results from storing a duration of time in a field that is meant
to contain a point in time.
I worked 8 hours and 15 minutes is a duration, I worked from 8:15AM is a
point in time.
The first is better stored as 495 (8*60 +15) minutes.

Access stores points in time in a DateTime field using a floating point
number that represents the value as on offset from midnight of Dec 30, 1899.
The integer portion represents the number of days and the portion after the
decimal point represents the time as a fraction of the day.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am looking to sum one text box containing hh:mm in a continuios report to
be calculated in the report footer. Here is the formula I have used that
does
not work.
=Sum("n", [TotalTime])\60

 

[John Spencer]

Try setting the control source of the textbox to one of the "formulas".

Control Source: = CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

That would all be on one line, the newsreader is probably going to wrap it
into two lines.

If it fails then post back with the exact error you are getting.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I truly appreciate the help, and patience. With the information provided
where do I put

Sum(CDbl(Nz([TotalTime],0)) (does this go in the text box?)

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

(also where does the above formula go to make this possbile?)

I just want to add all the hours and minuites in a text box in a footer.
Your asumptions where correct in that I am summing a text box only
contianing
hh:mm.
Thank you in advance!

Assumptions:
TotalTime is a DateTime field that contains only a time component.

Sum(CDbl(Nz([TotalTime],0)) should give you a number that represents
the
days and partial days (hours and minutes)

Then you should be able to convert that to hours and minutes using some
math.

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

The problem results from storing a duration of time in a field that is
meant
to contain a point in time.
I worked 8 hours and 15 minutes is a duration, I worked from 8:15AM is a
point in time.
The first is better stored as 495 (8*60 +15) minutes.

Access stores points in time in a DateTime field using a floating point
number that represents the value as on offset from midnight of Dec 30,
1899.
The integer portion represents the number of days and the portion after
the
decimal point represents the time as a fraction of the day.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am looking to sum one text box containing hh:mm in a continuios report
to
be calculated in the report footer. Here is the formula I have used
that
does
not work.
=Sum("n", [TotalTime])\60

 

[Roby]

I have put that formula in the control source of the text box, this is what
is says;

--The expression you entered contains invalid syntax, or you need to enclose
your text data in quotes.

You may have entered an invalid comma or omitted quotation marks.
For example, if the Default Value property of a text field is “Huey, Louie,
and Dewey,†it must be enclosed in quotes if you mean it as a literal text
string. This avoids the confusion with the expression “Huey Louie†And “Deweyâ€

I've been trying to get this for weeks, I really appreciate your help man!!!

Try setting the control source of the textbox to one of the "formulas".

Control Source: = CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

That would all be on one line, the newsreader is probably going to wrap it
into two lines.

If it fails then post back with the exact error you are getting.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I truly appreciate the help, and patience. With the information provided
where do I put

Sum(CDbl(Nz([TotalTime],0)) (does this go in the text box?)

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

(also where does the above formula go to make this possbile?)

I just want to add all the hours and minuites in a text box in a footer.
Your asumptions where correct in that I am summing a text box only
contianing
hh:mm.
Thank you in advance!

Assumptions:
TotalTime is a DateTime field that contains only a time component.

Sum(CDbl(Nz([TotalTime],0)) should give you a number that represents
the
days and partial days (hours and minutes)

Then you should be able to convert that to hours and minutes using some
math.

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

The problem results from storing a duration of time in a field that is
meant
to contain a point in time.
I worked 8 hours and 15 minutes is a duration, I worked from 8:15AM is a
point in time.
The first is better stored as 495 (8*60 +15) minutes.

Access stores points in time in a DateTime field using a floating point
number that represents the value as on offset from midnight of Dec 30,
1899.
The integer portion represents the number of days and the portion after
the
decimal point represents the time as a fraction of the day.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am looking to sum one text box containing hh:mm in a continuios report
to
be calculated in the report footer. Here is the formula I have used
that
does
not work.
=Sum("n", [TotalTime])\60

 

[John Spencer]

The number of left and right parentheses are out of whack. I think this may
be correct.

CInt(Sum(CDbl(Nz([TotalTime],0)) * 24)) & ": " &
Format(CInt(Sum(CDbl(Nz([TotalTime],0)) *60)) Mod 60, "00")

You need to test this carefully if it does work. It may work to give you
results, but the results may be inaccurate.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have put that formula in the control source of the text box, this is what
is says;

--The expression you entered contains invalid syntax, or you need to
enclose
your text data in quotes.

You may have entered an invalid comma or omitted quotation marks.
For example, if the Default Value property of a text field is "Huey,
Louie,
and Dewey," it must be enclosed in quotes if you mean it as a literal text
string. This avoids the confusion with the expression "Huey Louie" And
"Dewey"

I've been trying to get this for weeks, I really appreciate your help
man!!!

Try setting the control source of the textbox to one of the "formulas".

Control Source: = CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

That would all be on one line, the newsreader is probably going to wrap
it
into two lines.

If it fails then post back with the exact error you are getting.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I truly appreciate the help, and patience. With the information provided
where do I put

Sum(CDbl(Nz([TotalTime],0)) (does this go in the text box?)

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

(also where does the above formula go to make this possbile?)

I just want to add all the hours and minuites in a text box in a
footer.
Your asumptions where correct in that I am summing a text box only
contianing
hh:mm.
Thank you in advance!


:

Assumptions:
TotalTime is a DateTime field that contains only a time component.

Sum(CDbl(Nz([TotalTime],0)) should give you a number that
represents
the
days and partial days (hours and minutes)

Then you should be able to convert that to hours and minutes using
some
math.

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

The problem results from storing a duration of time in a field that is
meant
to contain a point in time.
I worked 8 hours and 15 minutes is a duration, I worked from 8:15AM is
a
point in time.
The first is better stored as 495 (8*60 +15) minutes.

Access stores points in time in a DateTime field using a floating
point
number that represents the value as on offset from midnight of Dec 30,
1899.
The integer portion represents the number of days and the portion
after
the
decimal point represents the time as a fraction of the day.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am looking to sum one text box containing hh:mm in a continuios
report
to
be calculated in the report footer. Here is the formula I have used
that
does
not work.
=Sum("n", [TotalTime])\60

 

[Roby]

Hey, I tried this formula and it gave me a #Error message. It's a step in the
right direction because it's not #Name anymore,lol. I appreciate your help!
Any other thoughts??

(I tried this formula in both a query and in the control box. Both shows the
same Error message)

The number of left and right parentheses are out of whack. I think this may
be correct.

CInt(Sum(CDbl(Nz([TotalTime],0)) * 24)) & ": " &
Format(CInt(Sum(CDbl(Nz([TotalTime],0)) *60)) Mod 60, "00")

You need to test this carefully if it does work. It may work to give you
results, but the results may be inaccurate.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have put that formula in the control source of the text box, this is what
is says;

--The expression you entered contains invalid syntax, or you need to
enclose
your text data in quotes.

You may have entered an invalid comma or omitted quotation marks.
For example, if the Default Value property of a text field is "Huey,
Louie,
and Dewey," it must be enclosed in quotes if you mean it as a literal text
string. This avoids the confusion with the expression "Huey Louie" And
"Dewey"

I've been trying to get this for weeks, I really appreciate your help
man!!!

Try setting the control source of the textbox to one of the "formulas".

Control Source: = CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

That would all be on one line, the newsreader is probably going to wrap
it
into two lines.

If it fails then post back with the exact error you are getting.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I truly appreciate the help, and patience. With the information provided
where do I put

Sum(CDbl(Nz([TotalTime],0)) (does this go in the text box?)

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

(also where does the above formula go to make this possbile?)

I just want to add all the hours and minuites in a text box in a
footer.
Your asumptions where correct in that I am summing a text box only
contianing
hh:mm.
Thank you in advance!


:

Assumptions:
TotalTime is a DateTime field that contains only a time component.

Sum(CDbl(Nz([TotalTime],0)) should give you a number that
represents
the
days and partial days (hours and minutes)

Then you should be able to convert that to hours and minutes using
some
math.

CInt(Sum(CDbl(Nz([TotalTime],0))*24) & ":" &
Format((Sum(CDbl(Nz([TotalTime],0))*60) Mod 60,"00")

The problem results from storing a duration of time in a field that is
meant
to contain a point in time.
I worked 8 hours and 15 minutes is a duration, I worked from 8:15AM is
a
point in time.
The first is better stored as 495 (8*60 +15) minutes.

Access stores points in time in a DateTime field using a floating
point
number that represents the value as on offset from midnight of Dec 30,
1899.
The integer portion represents the number of days and the portion
after
the
decimal point represents the time as a fraction of the day.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am looking to sum one text box containing hh:mm in a continuios
report
to
be calculated in the report footer. Here is the formula I have used
that
does
not work.
=Sum("n", [TotalTime])\60

 

[John Spencer]

Try building it up a step at a time,

Try just by using
= CDbl(Nz([TotalTime],0)

If that works then add
= CDbl(Nz([TotalTime],0) * 24

= SUM(CDbl(Nz([TotalTime],0) * 24)

= Int(SUM(CDbl(Nz([TotalTime],0) * 24))

If that works then use a similar process to get just the minutes in a
second calculated column.

Format(Int(Sum(CDbl(Nz([TotalTime],0)) *24 * 60)) Mod 60

 

[Balfour211]

I am looking to sum one text box containing hh:mm in a continuios report to
be calculated in the report footer. Here is the formula I have used that does
not work.
=Sum("n", [TotalTime])\60

This sounds similar to a question I posted in General Questions area. Here
is the answer I got. It worked for me. I got the answer from Tom Wickerath.
I hope this is kosher for me to use his response like this.....

The following is Tom Wickerath's reply:

See the Timesheet form in the sample that you can download with this article:

On time and how much has elapsed
http://office.microsoft.com/en-us/assistance/HA011102181033.aspx

The Control Source for the textbox in the subform's footer calls the
HoursAndMinutes function that is given in this article:

=HoursAndMinutes(Sum([TimeOut]-[TimeIn]))


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________