Google Sheets Countdown to Friday

Question

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.

Answer 1

You want to find the day and time when you finish your norm of working hours for this week. This is best done in a sequence of formulas, instead of one giant incomprehensible formula. Here's how I organized the computations in my example: assuming the business hours are 8:30-12 and 13-17:30 (that is, 12-13 is lunch break). I'm going to use 24-hour clock here.

Column A has the aforementioned parameters of the day: A1=8.5, A2=12, A3=13, A4=17.5, A5=A4-A3+A2-A1 (the latter is the number of work hours in a day).

Cell B1 has the number of hours left to work, for example 7.

Column C carries out the computations as follows:

• C1 = (now()-today())*24 is the current time in hours
• C2 = max(0,min(A2,C1)-A1) + max(0,min(A4,C1)-A3) is the number of work hours elapsed so far today.
• C3 = mod((C2+B1)/A5,1)*A5 is how many hours you spend working on the day when your target is reached.
• C4 = A1+C3 + (A1+C3>A2)*(A3-A2) converts C3 into normal hours of the day. This formula is the inverse of the formula in C2. It adds the number of hours to the beginning of work day; if the result is past the beginning of lunch, then the duration of lunch is added.
• C5 = today() + floor((C2+B1)/A5) + C4/24 is the final answer, it should be formatted as datetime. The formula uses the hours computed in C4, and the day obtained by incrementing today's date by how many whole business days fit in the amount C2+B1.

A screenshot of how this works: my local time is just past 17 hours on 11/24. If I have to work 7 hours this week, I'll be done 11/25 just past 16.