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

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

        HansVogelaar 

         

        https://docs.google.com/spreadsheets/d/1uZxv0JqEfS0i4R4IjoWdTIJyEY8h4m_u/edit?usp=sharing&ouid=104462821698118680185&rtpof=true&sd=true 

         

        Thank you for your update

        When I entered the new formula it got a little bit mixed up and give incorrect values for many shifts except the 8:00-20:00 shift and 20:00-8:00 shift 

         

        for Example 8:00 to 16:00 shift need to get a value of 7.30 hrs

         

        I have attached the original file, the hours in blue column need to be 11:00hrs, instead of 11:30.

         

        Rest of Hours Worked value in every other shift are ok with the old formula. 

         

        Kind Regards

         

        Could please have a look at the attached file one more time.

         

         

         

         

         

Resources