How do I add time like 8:20am-12:00pm and then after lunch break 12:30pm-5:02pm to end of day to get hours worked per day? I was using =(B2-A2*24)+(D2-A2*24) but that doesn't work
Google-sheets – Calculating time worked per day
google sheets
Related Solutions
If you use this formula, then all circumstances will be taken into account:
=ARRAYFORMULA(
IF(J2:J6<>"",J2:J6-C2:C6,
IF(H2:H6<>"",H2:H6-C2:C6,
IF(F2:F6<>"",F2:F6-C2:C6,
IF(D2:D6<>"",D2:D6-C2:C6))))
-ARRAYFORMULA(
IF(E2:E6<>"",E2:E6-D2:D6)+
IF(I2:I6<>"",I2:I6-H2:H6)+
IF(G2:G6<>"",G2:G6-F2:F6)))
See your own file for the result.
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())*24is the current time in hoursC2 = 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)*A5is 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/24is 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.
Related Topic
- Google Sheets – Time Tracking Methods
- Google-sheets – Calculate the amount paid according to time in/out with an optional break in between
- Google-sheets – How to prevent or omit a negative value when calculating between times
- Google-sheets – How to output end task time base on start time, duration and working time in Google Sheets
- Google-sheets – How to modify the formula to take 1 hour off
- Google-sheets – Calculate hours worked but not limited to a single day
Best Answer
It looks like you've got some order of operations trouble. I think this will give you what you want:
Of course, you also need to ensure that the cell uses a decimal number format. And that your afternoon values need to be actual afternoon values. You need to use 24-hour format or am/pm format.
In any event, using your values above, this formula, and appropriate formatting, this is what I get: