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?

  • I added some alternatives to H9:H11 (minutes as time) and H12:H14 (minutes as integers). The MROUND function was used to round off to the closest minute and avoid floating point errors like those in G10:G12. – user82329 May 14 '15 at 7:55

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 ?

  • I will look into at once. – Allan Bech May 12 '15 at 10:14
  • Seems to fail in B13. When time is 18.00 it still counts from 17.00. – Allan Bech May 12 '15 at 10:22
  • I changed the formula. Can you check again ? – JPV May 12 '15 at 11:49
  • Just perfect! Only I don' understand the use of len and timevalue. – Allan Bech May 12 '15 at 12:14
  • 1
    Glad to hear. The len() function is there to check if there is something in the cells of col B and col C (=check if they are not blank). For the function timevalue() see also here: support.google.com/docs/answer/3267350?hl=en. Please accept my answer if the solution works for you. Cheers, JP – JPV May 12 '15 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.