Ahh…
I was reading too quickly, then, and missed the "so" at the end of the first line.
The OT (column D) and ST (column E) columns should both be being calculated by formulas.
The one in E2 shuld be similar to this: DUR2HOURS(MIN(C2-B2,"8h"))
This calculates the duration (time that has passed) from the start time to the finish time, capping the result at 8h, then converts that result to a number representing the number of hours worked, excluding any over 8.
The formula in D2 should be similar: DUR2HOURS(MAX(C2-B2-DURATION(,,8),"0h")
These do the actual calculation, assuming this table is independent of any other tables. They also may not work as direct replacements for the existing formulas in those two cells. I've not tested either, and won't have an opportunity to do so for a few hours.
Meantime, please reply with a copy of the current formulas in these two cells: D2 and E2.
Regards,
Barry
I notice you have marked one of your posts Solved.
Considering that the screen shot included in that post shows these results:
I question how you interpret this as being "Solved."
For Monday, you have a start time of 21:00 and a finish time of 06:00 (the next day). The difference here is nine hours, which should be displayed as 1.00 (hours) of overtime and 8.00 (hours) of Standard time.
In the screen shot, it displays as 0.00 (hours) of overtime and (negative) 15.00 (hours) of standard time.
What has probably occurred here is that you entered both time values on the same day. ALL time (of day) values displayed in Numbers are the time part of a Date & Time value marking an instant in time. If you enter only the Time part, Numbers automatically sets the Date part to the day of the entry being made. If both Monday values were entered as time (without the date), both times have been entered wth the same date, and the subtraction of 'today' at 22:00 from 'today' at 06:00 returns a duration of -15 hours.
The formulas I provided took NO account of the date on which the times were entered. The original formulas in columns D and E Likely did account for that possibility. That is the reason I asked to see the original formulas in cells D2 and E2; formulas which, as yet, you have not revealed.
Your most recent screen shots do display formulas in the Formula Editor, but they, as you note in the following post, are "the same as you (I) posted" which is not what I requested. In addition, the displays include only the beginnings of those formulas; not particularly useful information when trying to analyze the full formula.
Tuesday (row 3) of your screenshot also shows an error. The start time on this row is 22:00, the finish time is 06:00. The duration from 22:00 to 06:00 is 8 hours. I have no idea how your table gets from a duration of 8 hours from start to finish to 8 hours of standard time AND 2.50 hours of overtime.
While those two rows are providing results with errors, the rest are providing the correct results for Wednesday to Friday, where start and finish times are the same as those on Tuesday. I am hoping this means you have not yet altered the formulas in these rows.
IF that IS the case, please:
Regards,
Barry
"When I change my working hours eg starting at 18:00 instead of 22:00 so it then automatically displays in Overtime as in this occasion 4.00."
Your screen shot does not show "this occasion", nor does it show "4:00" in Overtime.
When you change your start time to 18:00, do you aso change your finish time to 02:00? If not, that would explain the 4:00 overtime.
Regards,
Barry
Those are the formulas in cells D9, E9 and E10. They are correct.
But I asked for the formulas in D2 and E2, the first cell in each column containing the formula that now calculates the daily overtime and standard time values.
Formulas in the cells above these two:
Regards,
Barry
I want it to automatically enter into the overtime box
those are the same as you posted 👍
Time Sheet Overtime Help
