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=sharingI 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?
If time is before 17.00 use 17.00 OR if time is after 17.00 use value in cell
google spreadsheets;formulas
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 ? 