| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
| Time Totals in a Workbook | Excel Worksheet Functions | |||
| NOW Function For Time Changes With Each New Macro | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
Linear Mode
