# Adding hours beyond 24 hours

#### hoyos

##### Board Regular
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?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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?

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

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?

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

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
FGHIJK
410:00 PM10:00 PM24:00TOTAL HRS:34:30
510:00 PM8:30 AM10:30
Sheet1

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.

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.)

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>

 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.

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.

Replies
6
Views
133
Replies
1
Views
105
Replies
8
Views
171
Replies
6
Views
299
Replies
1
Views
155

1,219,798
Messages
6,150,318
Members
450,951
Latest member
kh198

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back