ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   adding up time function totals (https://www.excelbanter.com/excel-worksheet-functions/122072-adding-up-time-function-totals.html)

italiavb
adding up time function totals
 
Hi, I am trying to add up a column of time format (h:mm) daily totals. I
can't determine what I am doing wrong: Any help would be appreciated.
January
Day
in out TOTAL in out TOTAL DAILY TOTAL
1 6:30:00 AM 11:00:00 AM 4:30 3:30:00 PM 5:30:00 PM 2:00 6:30 6.50
2 7:00:00 AM 11:00:00 AM 4:00 2:00:00 PM 6:30:00 PM 4:30 8:30 8.50
3 8:00:00 AM 12:00:00 PM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
4 7:00:00 AM 11:00:00 AM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
5 0:00 0:00
6 0:00 0:00
7 0:00 0:00
8 0:00 0:00
9 0:00 0:00
10 0:00 0:00
11 0:00 0:00 $22.00 PAY
12 0:00 0:00
13 0:00 0:00
14 0:00 0:00
15 0:00 0:00

6:00 6.00 $132.00
TOTAL HRS WK CONVERSION TOTAL GROSS EARNINGS

Fred Smith
adding up time function totals
 
Could you give us a hint as to what formula you are using?

--
Regards,
Fred


"italiavb" wrote in message
...
Hi, I am trying to add up a column of time format (h:mm) daily totals. I can't
determine what I am doing wrong: Any help would be appreciated.
January
Day
in out TOTAL in out TOTAL DAILY TOTAL
1 6:30:00 AM 11:00:00 AM 4:30 3:30:00 PM 5:30:00 PM 2:00 6:30 6.50
2 7:00:00 AM 11:00:00 AM 4:00 2:00:00 PM 6:30:00 PM 4:30 8:30 8.50
3 8:00:00 AM 12:00:00 PM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
4 7:00:00 AM 11:00:00 AM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
5 0:00 0:00
6 0:00 0:00
7 0:00 0:00
8 0:00 0:00
9 0:00 0:00
10 0:00 0:00
11 0:00 0:00 $22.00 PAY
12 0:00 0:00
13 0:00 0:00
14 0:00 0:00
15 0:00 0:00

6:00 6.00 $132.00
TOTAL HRS WK CONVERSION TOTAL GROSS EARNINGS

David McRitchie
adding up time function totals
 
Looks to me like you have correct totals in the rows:

C2: = B2-A2 + (B2<A2)
F2: = E2-D2 + (E2<D2)
G2: = 24 * (C2 + G2) and formatted as 0.00

G22 = SUM(G$2:OFFSET(G22,-1,0)

You may be skipping over a problem with working with Excel Time
which is measured as a fraction of a day so when you want to convert
Excel time to decimal you must multiply by 24 to convert fraction of day
to hours.

If your totals were of Excel time then to prevent the overflow
of hours into days you would format as [hh]:mm

More information on date and time in
http://www.mvps.org/dmcritchie/excel/datetime.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Fred Smith" wrote in message ...
Could you give us a hint as to what formula you are using?

--
Regards,
Fred


"italiavb" wrote in message
...
Hi, I am trying to add up a column of time format (h:mm) daily totals. I can't
determine what I am doing wrong: Any help would be appreciated.
January
Day
in out TOTAL in out TOTAL DAILY TOTAL
1 6:30:00 AM 11:00:00 AM 4:30 3:30:00 PM 5:30:00 PM 2:00 6:30 6.50
2 7:00:00 AM 11:00:00 AM 4:00 2:00:00 PM 6:30:00 PM 4:30 8:30 8.50
3 8:00:00 AM 12:00:00 PM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
4 7:00:00 AM 11:00:00 AM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
5 0:00 0:00
6 0:00 0:00
7 0:00 0:00
8 0:00 0:00
9 0:00 0:00
10 0:00 0:00
11 0:00 0:00 $22.00 PAY
12 0:00 0:00
13 0:00 0:00
14 0:00 0:00
15 0:00 0:00

6:00 6.00 $132.00
TOTAL HRS WK CONVERSION TOTAL GROSS EARNINGS

italiavb
adding up time function totals
 
i've attached a spread sheet.
"Fred Smith" wrote in message
...
Could you give us a hint as to what formula you are using?

--
Regards,
Fred


"italiavb" wrote in message
...
Hi, I am trying to add up a column of time format (h:mm) daily totals. I
can't determine what I am doing wrong: Any help would be appreciated.
January
Day
in out TOTAL in out TOTAL DAILY TOTAL
1 6:30:00 AM 11:00:00 AM 4:30 3:30:00 PM 5:30:00 PM 2:00 6:30 6.50
2 7:00:00 AM 11:00:00 AM 4:00 2:00:00 PM 6:30:00 PM 4:30 8:30 8.50
3 8:00:00 AM 12:00:00 PM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
4 7:00:00 AM 11:00:00 AM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
5 0:00 0:00
6 0:00 0:00
7 0:00 0:00
8 0:00 0:00
9 0:00 0:00
10 0:00 0:00
11 0:00 0:00 $22.00 PAY
12 0:00 0:00
13 0:00 0:00
14 0:00 0:00
15 0:00 0:00

6:00 6.00 $132.00
TOTAL HRS WK CONVERSION TOTAL GROSS EARNINGS

italiavb
adding up time function totals
 
I now need to total the column.....I can't seem to get there. =SUM(H4:H8)
doesn't work. Yet this =SUM(H4:H5) works. In fact I can't seem to sum more
then 3 rows in the h:mm format.
Thanks for your help. I'll keep at it.

"David McRitchie" wrote in message
...
Looks to me like you have correct totals in the rows:

C2: = B2-A2 + (B2<A2)
F2: = E2-D2 + (E2<D2)
G2: = 24 * (C2 + G2) and formatted as 0.00

G22 = SUM(G$2:OFFSET(G22,-1,0)

You may be skipping over a problem with working with Excel Time
which is measured as a fraction of a day so when you want to convert
Excel time to decimal you must multiply by 24 to convert fraction of day
to hours.

If your totals were of Excel time then to prevent the overflow
of hours into days you would format as [hh]:mm

More information on date and time in
http://www.mvps.org/dmcritchie/excel/datetime.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Fred Smith" wrote in message
...
Could you give us a hint as to what formula you are using?

--
Regards,
Fred


"italiavb" wrote in message
...
Hi, I am trying to add up a column of time format (h:mm) daily totals.
I can't
determine what I am doing wrong: Any help would be appreciated.
January
Day
in out TOTAL in out TOTAL DAILY TOTAL
1 6:30:00 AM 11:00:00 AM 4:30 3:30:00 PM 5:30:00 PM 2:00 6:30 6.50
2 7:00:00 AM 11:00:00 AM 4:00 2:00:00 PM 6:30:00 PM 4:30 8:30 8.50
3 8:00:00 AM 12:00:00 PM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
4 7:00:00 AM 11:00:00 AM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
5 0:00 0:00
6 0:00 0:00
7 0:00 0:00
8 0:00 0:00
9 0:00 0:00
10 0:00 0:00
11 0:00 0:00 $22.00 PAY
12 0:00 0:00
13 0:00 0:00
14 0:00 0:00
15 0:00 0:00

6:00 6.00 $132.00
TOTAL HRS WK CONVERSION TOTAL GROSS EARNINGS

daddylonglegs
adding up time function totals
 
Can't see any attachment...

from your previous post you say that the SUM "doesn't work"? What do you
mean. If you get 6:00 hours returned instead of 30:00 as expected then, As
david suggests, format the cell with your total hours as [h]:mm.

This format allows totals over 24 hours, h:mm format doesn't

"italiavb" wrote:

i've attached a spread sheet.
"Fred Smith" wrote in message
...
Could you give us a hint as to what formula you are using?

--
Regards,
Fred


"italiavb" wrote in message
...
Hi, I am trying to add up a column of time format (h:mm) daily totals. I
can't determine what I am doing wrong: Any help would be appreciated.
January
Day
in out TOTAL in out TOTAL DAILY TOTAL
1 6:30:00 AM 11:00:00 AM 4:30 3:30:00 PM 5:30:00 PM 2:00 6:30 6.50
2 7:00:00 AM 11:00:00 AM 4:00 2:00:00 PM 6:30:00 PM 4:30 8:30 8.50
3 8:00:00 AM 12:00:00 PM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
4 7:00:00 AM 11:00:00 AM 4:00 1:30:00 PM 5:00:00 PM 3:30 7:30 7.50
5 0:00 0:00
6 0:00 0:00
7 0:00 0:00
8 0:00 0:00
9 0:00 0:00
10 0:00 0:00
11 0:00 0:00 $22.00 PAY
12 0:00 0:00
13 0:00 0:00
14 0:00 0:00
15 0:00 0:00

6:00 6.00 $132.00
TOTAL HRS WK CONVERSION TOTAL GROSS EARNINGS


All times are GMT +1. The time now is 02:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com