Forum Discussion
Time formula
Can anybody please give me the formula to make this work. I have tried for many hours and just can't get it. Thank you for any help.
| DATE | TIME COME | TIME GO | HOURS WORKED |
| 1 | 0:00:00 | ||
| 2 | 0:00:00 | ||
| 3 | 0:00:00 | ||
| 4 | 0:00:00 | ||
| 5 | 0:00:00 | ||
| 6 | 0:00:00 | ||
| 7 | 0:00:00 | ||
| 8 | 0:00:00 | ||
| 9 | 0:00:00 | ||
| 10 | 0:00:00 | ||
| 11 | 0:00:00 | ||
| 12 | 0:00:00 | ||
| 13 | 0:00:00 | ||
| 14 | 0:00:00 | ||
| 15 | 0:00:00 | ||
| 16 | 0:00:00 | ||
| 17 | 0:00:00 | ||
| 18 | 0:00:00 | ||
| 19 | 0:00:00 | ||
| 20 | 0:00:00 | ||
| 21 | 0:00:00 | ||
| 22 | 0:00:00 | ||
| 23 | 0:00:00 | ||
| 24 | 0:00:00 | ||
| 25 | 0:00:00 | ||
| 26 | 1:45:00 | 5:50:00 | 4:05:00 |
| 27 | 11:20:00 | 7:45:00 | #################### |
| 28 | 11:30:00 | 7:30:00 | #################### |
| 29 | 11:00:00 | 8:00:00 | 21:00:00 |
| 30 | 2:00:00 | 8:00:00 | 6:00:00 |
| 31 | 0:00:00 | ||
| TOTAL HOURS WORKED | 0:00:00 |
12 Replies
- Try this in D2:
=C2-B2+
(C2<=B2) Alternatively:
Suppose your start and end times are in A1 and B1, then in C1:
=IF(A1>B1,1-A1+B1,B1-A1)Also important to enter times so that Excel knows if it is e.g. 11:30 AM or 11:30 PM (i.e. 23:30). In your example, the formula probably did 7:30 (AM) minus 11:30 (AM). That's a negative and your result will ######### if formatted as a time.
Yes, it works, thank you, just the total does not work, any idea? OK, I got the total to work. Thank you very much. I'll buy you a beer if you ever come to my bar in Thailand. Cheers.
Glad you solved the total yourself. I'll let you know when I'm near 🙂
- Thank you so much for taking the time to help, much appreciated. I will try and see how it goes. Thanks again.
