Adding hours beyond 24 hours

[hoyos]

I need to add daily hours of work, an average days works adds up to 19 hours. I need to add at least one months worth of hours. Any ideas as to the best formula to use?

 

[Robert Mika]

I need to add daily hours of work, an average days works adds up to 19 hours. I need to add at least one months worth of hours. Any ideas as to the best formula to use?

Could post an example?

 

[oldbrewer]

sum(A1:A10) gives the total of the numbers in the first 10 rows of column A

 

[hoyos]

I have this code, if I fill it down the colunm and try to add the values up it returns #VALUE

=if(isblank(g4),"",if(isblank(f4),"",if(g4-f4=0,"24:00",if(g4-f4<0,24+g4-f4,g4-f4))))

Any ideas?

 

[oldbrewer]

ADVERTISEMENT

what is in columns f and g ? are you calculating the hours worked based on start time and finish time ?

 

[Scott Huish]

Try this formula and format the cell as [h]:mm

=IF(COUNT(F4:G4)<2,"",IF(F4=G4,"24:00"+0,MOD(G4-F4,1)))

Excel Workbook
	F	G	H	I	J	K
4	10:00 PM	10:00 PM	24:00		TOTAL HRS:	34:30
5	10:00 PM	8:30 AM	10:30
Sheet1

 

[Robert Mika]

ADVERTISEMENT

You have not still provided any data hopfully Scott solution will work.
On the other hand have you tried to use Custom Format [h]:mm?
This will go beyond 24 hours.

 

[oldbrewer]

If the start time and end time were entered as dd:mm:yy hh:mm then hours worked would be end time minus start time - much simpler.

(the visible formatting could be hh:mm to make it look prettier.)

 

[hoyos]

Try this formula and format the cell as [h]:mm

=IF(COUNT(F4:G4)<2,"",IF(F4=G4,"24:00"+0,MOD(G4-F4,1)))

Sheet1

*	F	G	H	I	J	K
4	10:00 PM	10:00 PM	24:00	*	TOTAL HRS:	34:30
5	10:00 PM	8:30 AM	10:30	*	*	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 91px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula H4 =IF(COUNT(F4:G4)<2,"",IF(F4=G4,"24:00"+0,MOD(G4-F4,1))) K4 =SUM(H:H) H5 =IF(COUNT(F5:G5)<2,"",IF(F5=G5,"24:00"+0,MOD(G5-F5,1))) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you I shall try your suggestion and get back to you.

 

[hoyos]

You have not still provided any data hopfully Scott solution will work.
On the other hand have you tried to use Custom Format [h]:mm?
This will go beyond 24 hours.

Sorry about that, I tried to attach the file but got denied. I shall try Scotts suggestion first. Sorry for any inconvenienvce.