#### Timmy_r

##### New Member
Hello,
I am currently using the formula =IF(C2>C1,1,0)+C2-C1-C3 to work out my hours worked per day. I then use the normal sum formula to add up hours worked for the week, however does not add correctly.

eg.

9:00 9:00 9:00
17:30 17:30 17:30
0:30 0:30 0:30
8:00 8:00 8:00 = 96:00

it adds up to 96 when it should be 24 hours

Is their a formula that will add the time per day, and then total for the week using that formula??

Thank you
Tim

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Timmy_r said:
Hello,
I am currently using the formula =IF(C2>C1,1,0)+C2-C1-C3 to work out my hours worked per day. I then use the normal sum formula to add up hours worked for the week, however does not add correctly.

eg.

9:00 9:00 9:00
17:30 17:30 17:30
0:30 0:30 0:30
8:00 8:00 8:00 = 96:00

it adds up to 96 when it should be 24 hours

Is their a formula that will add the time per day, and then total for the week using that formula??

Thank you
Tim

Try,

=C2-C1+(C2 < C1)-C3

The 1 in your original formula is adding one day, thus it's actually 32 hours, but your formatting of h:mm displays only eight. If you format the cell as [h]:mm, you'll see what I mean.

Thanks alot,
Thats exactly what i was after

Replies
6
Views
116
Replies
1
Views
525
Replies
7
Views
140
Replies
3
Views
667
Replies
0
Views
107

1,202,990
Messages
6,052,958
Members
444,621
Latest member
MIKOLAJ_R

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

### Which adblocker are you using?

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

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