This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
| 7:30 | 15:45 | 1:00 | 1:30 |
|---|---|---|---|
| 7:45 | 16:00 | 1:00 | 1:00 |
| 8:00 | 16:15 | 1:00 | 1:30 |
| 8:15 | 16:30 | 1:00 | 1:30 |
| 8:30 | 16:45 | 1:00 | 1:00 |
I have a specific problem with calculating time in Excel. Idea is easy: working hours and overtime. I have four colums: arriving, leaving, overtime ja cashing. Arriving and Overtime are hh:mm based, like 07:00 and 16:00. Overtime is leaving minus arriving minus daily worktime that is 7h 15min. Cashing is that when the overtime is 1h to 1h 59min, it is multiplied with 1,5. And if 2 hours or more, it's multiplied with 2. Everything under 1 hour goes to overtime bank.
It works perfectly. On every other arriving hour except 07:45 and 08:30. And it's driving me nuts. Everything is copied so the formulas and cell editing are all the same. 1 hour overtime, which is multiplied by 1,5, gives 1:30. As it should. But when I input arriving time at 07:45 and leaving 16:00, it shows overtime 1 hour, but doesn't multiply it. Same with 08:30 to 16:45. Every other variation works, ex. 08:15 to 16:30. 1 hour overtime, gives 1:30.
On overtime the formula is =IF(C4="","",(C4-B4)-TIME(7,15,0))
On caching the formula is a hellish thing: = IF(D4<0,IF(D4=TIME(0,0,0),IF(C4="","",IF(AND(D4<TIME(2,0,0),D4>=TIME(1,0,0)),D4*1,5,IF(D4>TIME(2,0,0),(TIME(3,0,0)+((D4-TIME(2,0,0))*2)),""))),D4),IF(D4=TIME(0,0,0),D4,IF(C4="","",IF(AND(D4<TIME(2,0,0),D4>=TIME(1,0,0)),D4*1,5,IF(D4>TIME(2,0,0),(TIME(3,0,0)+((D4-TIME(2,0,0))*2)),D4)))))
There has to be an easier way of doing this, but for now it works on every other part than that 07:45 and 08:30 arriving. I've checked all settings, formatting, cells and even went thru with it with ChatGPT, which actually gave good ideas to simplifying the code.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
This is limitation in computer calculation.
Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Learn
When you change it to number, you will see the difference.
As workaround, you may add a second in your formula.
=IF(C4="","",(C4-B4)-TIME(7,14,59))
You are welcome:) You may also give feedback on it.
Now it works. Thank you so much :D