Forum Discussion

joem1964's avatar
joem1964
Copper Contributor
Apr 14, 2021
Solved

Help with calculating empty Time data in cells

I am attempting to create a Roster Spreadsheet to calculate hours workedin a week (the hours worked are calculated in a hidden field), The tempolate must cater for days off , i.e. no times entered, b...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Apr 14, 2021

    joem1964 

    In H7:

     

    =IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(MOD(I6-H6,1)>=7/24)*$F6/1440,1/1440))

Kuri_91's avatar
Kuri_91
Copper Contributor
Jul 07, 2022

HansVogelaar 

 

Minimum 10 Hours worked for 60 minute break
Minimum 7 Hours worked for 30 minute break

HansVogelaar's avatar
HansVogelaar
MVP
Jul 07, 2022

Kuri_91 

So the formula becomes

 

=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(I6>H6)*((MOD(I6-H6,1)>=7/24)+(MOD(I6-H6,1)>=10/24))/48,1/1440))

  • Kuri_91's avatar
    Kuri_91
    Copper Contributor
    Jul 07, 2022

    HansVogelaar 

     

    Really thankful for the solution, appreciate your patience and prompt responses.

     

    It works like a magic

     

    Many Thanks

    Kuri

Resources