I have a formula:
=IF(WEEKDAY(TODAY()) = 6, (SUM(NOW() + $E$3)),
"Only Works on Friday during lunch for now!")
The point of this formula is to use info from another cell($E$3
) which counts down from 40 to 0, which predicts what time you'll hit 40 hours, which should help manage overtime. I have "working" log in, lunch starts, lunch ends, and log out fields which calculate daily time in hours on the right end of each row. Then, these cells are added together for a shared cell to count up regular hours in cell B21
. $E$3
contains the formula:
=SUM((40 - B21)/24)
formatted as a duration, which counts down from 40.
So, I can calculate regular hours, overtime, sick, holiday, etc., and I can figure out how many more hours I need to work until I hit my 40. What I need that initial if formula to do, is to take normal workday start and end times from two other cells, normal start time and normal end time. Then, use that to create a "normal" schedule, so that if I clock back in from lunch on Thursday (say 12:30pm), it will see that I have 13 hours left. Then, instead of just adding 13 hours to the current time, putting me somewhere around 2 in the morning, would rollover after the set end of the work day Thursday(say 5:30pm), and show that based on having 13 hours left, I would hit 40 at 5:30 on Friday. This is my first question, so please let me know if I've failed to include needed information, or made some other new guy mistake.