CODEWITHSUNDEEP
if-statementgoogle-sheetstimegoogle-sheets-formulaformula
Caramel_Twist
Caramel_Twist

Reputation: 21

Formula for calculating night shift times in Google Sheets

I've been wracking my brains for hours, trying to calculate duration of people shifts in an excel sheet. It is the hospitality industry, so they start in the afternoon and finish either midnight or 1am.

I am trying to add a weekly total hours column to their rota sheet, that in theory will auto update when new hours are inserted.

But I am having a problem where Google sheets will show me a weird duration in a negative number, or resets when it goes past 24 hours.

I will show down below.

Week Starting: 1st May Monday Tuesday Wednesday Thursday Friday Saturday Sunday Total
Bobby OFF OFF 4 PM 5 PM 4 PM 4P M 5 PM 16:00:00
Grace OFF OFF DELIVERY OFF IN IN OFF -------
Howard 5 PM OFF 6 PM OFF 5 PM 6 PM 3 PM --------
Katie OFF 5 PM OFF OFF OFF 12 PM 12 PM --------
David OFF OFF OFF OFF OFF 7 PM OFF --------
Max OFF OFF OFF OFF OFF 7 PM OFF --------
Events Event Event Event --------
Weekly Tasks Restock Clean Delivery Restock Clean Restock Clean --------

Here we see the table I am working with. The Rota is laid out to display people start time. this is formatted as TIME[HH]:[AM/PM], so in reality where a cell says 4 PM, this translates to 4:00:00 PM, and so on. I do not have an END TIME on the Table and cannot change that (Employers request).

People have regular end times, on Monday/Tuesday/Wednesday/Thursday/Sunday, people finish work at midnight (00:00:00), while on Friday/Saturday people finish at 1am (01:00:00).

I also have cells that don't state anytime, but states 'OFF' denoting that someone is not working that day.

I need to make a column; shown here as 'Total' that shows the total hours someone has worked that week.

NOTE I don't need to calculate totals for Grace

I have gotten as far as calculating the hours for Bobby using the formula:

= (If(B2<>"Off", (TIME(24,0,0) - B2),"0") + 
  If(C2<>"Off", (TIME(24,0,0) - C2),"0") + 
  If(D2<>"Off", (TIME(24,0,0) - D2),"0") + 
  If(E2<>"Off", (TIME(24,0,0) - E2),"0") + 
  If(F2<>"Off", (TIME(25,0,0) - F2),"0") + 
  If(G2<>"Off", (TIME(25,0,0) - G2),"0") + 
  If(H2<>"Off", (TIME(24,0,0) - H2),"0"))

Which gives me 16:00:000 when I am expecting 40:00:00 this is when formatted at TIME As it goes over 24 hours the number resets to 00:00:00 When I format the number as Duration in Google Sheets, it shows -80:00:00 Which is double what I am expecting and a negative number.

I am also aware I need to account for the presence of Delivery which complicates matters.

My brain is broken and I am confused, I have seen suggestions of using FormulaArray, but am at a loss of how and why to use this.

How do I go about calculating the hours here.

Upvotes: 1

Views: 948

Answers (2)

rockinfreakshow
rockinfreakshow

Reputation: 30240

You may try this arrayformula()

=map(A2:A,lambda(Λ,if(Λ="",,sum(index(let(Σ,offset(Λ,,1,,7),iferror(if(regexmatch(B1:H1,"^(Fr|Sa)"),if(Σ="",,"25:00:00"-Σ),if(Σ="",,"24:00:00"-Σ)))))))))

enter image description here

Upvotes: 1

doubleunary
doubleunary

Reputation: 19155

16:00 is the time of the day when 40 hours have passed from a midnight. To display the result as duration rather than as time of day, format the result cell as Format > Number > Duration. To do the same with a formula, use query(), like this:

=query( 
  if(B2 <> "Off", "24:00" - B2, "0:00") + 
  if(C2 <> "Off", "24:00" - C2, "0:00") + 
  if(D2 <> "Off", "24:00" - D2, "0:00") + 
  if(E2 <> "Off", "24:00" - E2, "0:00") + 
  if(F2 <> "Off", "25:00" - F2, "0:00") + 
  if(G2 <> "Off", "25:00" - G2, "0:00") + 
  if(H2 <> "Off", "24:00" - H2, "0:00"), 
  "format Col1 '[h]:mm' ", 0 
)

See Working with date and time values in Google Sheets.

Upvotes: 0

Related Questions