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

joem1964's avatar
joem1964
Copper Contributor
Apr 22, 2021
Hello Hans, Many thanks for your help and this resolved the issue for me, however, I now need to also cater for night shifts, apparently they work 12 hour shifts without the deduction of Breaks, can that be incorporated in the same formula?
(Night shifts typically atart at 19:45 until 07: 45 or 20:00 to 08:00)

Joe
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

  • joem1964's avatar
    joem1964
    Copper Contributor
    Apr 22, 2021
    Hello Hans,

    Yes, I have tested with various times and it works perfectly, thank you very much for your halp again, Joe