Google Sheets – Conditional Time-Based Cell Values

formulasgoogle sheets

Headline says it all. I'm trying to calculate worktime in minutes. Ex: https://docs.google.com/spreadsheets/d/1GmGyi4kQf8I2qvEJjfG3OZvm_n_kBbsw_doTi9J0KCk/edit?usp=sharing

I have to state when meeting start and when it stops. Overtime is from 17.00 so:
If a meeting is starting at 16.00 and last till 21.00 it, result must be 4 hours = 240 minutes.
But if a meeting starts at 18.00 and last till 21.00, result must 3 hours = 180 minutes.

Any suggestions?

Best Answer

In cell G9 of the spreadsheet you shared, I entered this formula:

=arrayformula(if(len(B9:B33)*len(C9:C33)*(C9:C33>B9:B33);if(timevalue(B9:B33)<timevalue("17.00.00"); mod(C9:C33-time(17;0;0);1)*1440; mod(C9:C33-B9:B33;1)*1440);))

It will only output a result when there are values in col B and col C and when col C > col B. See if this helps ?

Related Topic