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))

HansVogelaar's avatar
HansVogelaar
MVP
Apr 22, 2021

joem1964 

Does this do what you want?

 

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

Kuri_91's avatar
Kuri_91
Copper Contributor
Jul 06, 2022

Hello Hans,

Many thanks for the formula, there are people working from 8 to 12 hr shift get 1 Hour break.

How do incorporate that into this formula?

Day -0 to 7Hrs = No Break
Day -7 - 8 hrs = 30mnts
Day 8 - 12 Hrs  = 1hr Break
Night Shift = No Break

Using the same sheet as reference

Regards
Kuri

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

      HansVogelaar 

       

      Day Shift

      8:00 am to 3pm - No break ( 7 Hours)

      8:00am to 3:30pm - 30mnts Break ( More than 7 Hrs)

      8:00 am to 5 pm - 30mnts Break ( 8 Hours)

      8:00am to 8 pm - 1 hrs Break (12 Hours)

      Night Shift

      8:00pm to 8 am ( Night Shift) - No Break

       

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

       

      Everything else is ok except the 12Hr shift, it is taking only 30mnts break.

       

       

      thanks

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 06, 2022

        Kuri_91 

        In H7:

         

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