SOLVED

Help with calculating empty Time data in cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2272189%22%20slang%3D%22en-US%22%3EHelp%20with%20calculating%20empty%20Time%20data%20in%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2272189%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CFONT%3EI%20am%20attempting%20to%20create%20a%20Roster%20Spreadsheet%20to%20calculate%20hours%20workedin%20a%20week%20(the%20hours%20worked%20are%20calculated%20in%20a%20hidden%20field)%2C%20The%20tempolate%20must%20cater%20for%20days%20off%20%2C%20i.e.%20no%20times%20entered%2C%20but%20the%20formulae%20needs%20to%20be%20able%20to%20cater%20for%20this%20or%20else%20%23NUM%20appears%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3E%26nbsp%3B18%26nbsp%3B%26nbsp%3B19%26nbsp%3B%26nbsp%3B20%26nbsp%3B%26nbsp%3B21%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3BSun%26nbsp%3B%26nbsp%3BMon%26nbsp%3B%26nbsp%3BTues%26nbsp%3B%26nbsp%3BWed%26nbsp%3B%3CBR%20%2F%3EHours%20Worked%26nbsp%3BStart%26nbsp%3BEnd%26nbsp%3BStart%26nbsp%3BEnd%26nbsp%3BStart%26nbsp%3BEnd%26nbsp%3BStart%26nbsp%3BEnd%3CBR%20%2F%3E%23NUM!%26nbsp%3B%26nbsp%3B%26nbsp%3B20%3A00%26nbsp%3B8%3A00%26nbsp%3B8%3A00%26nbsp%3B20%3A00%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%23NUM!%26nbsp%3B%26nbsp%3B11%3A30%26nbsp%3B%26nbsp%3B11%3A30%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EThe%20daily%20hours%20worked%20are%20totaled%20in%20the%20hours%20worked%20column.%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3EA%20second%20issue%20I%20have%20is%20when%20the%20hours%20worked%20is%20%26lt%3B%207%20hours%20%2C%20then%20the%20lunch%20break%20is%20not%20deducted.%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2272189%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor
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, but the formulae needs to be able to cater for this or else #NUM appears
 18  19  20  21 
 Sun  Mon  Tues  Wed 
Hours Worked Start End Start End Start End Start End
#NUM!   20:00 8:00 8:00 20:00  
 #NUM!  11:30  11:30   
The daily hours worked are totaled in the hours worked column.
        
A second issue I have is when the hours worked is < 7 hours , then the lunch break is not deducted.
5 Replies
best response confirmed by joem1964 (New Contributor)
Solution

@joem1964 

In H7:

 

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

Thanks Hans, this has worked perfectly and I will now amend the spreadsheet to reflect the new formula, Many thanks again
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

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

Hello Hans,

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