I'm working on a 7X24 roster for about 20 employees that will span over a 5-week period for a special event. The sample I have attached only shows 3 employees over a shorter time period. I'm calculating the hours between shifts to ensure I have adequate rest time between workdays. I can't figure out how to best deal with days where an employee goes from a night shift to a day shift. For example Emp 3 works on Day 1 works from 23:00 to 08:00 the next day. He is off on Day 2 and starts again at midnight Day 3. My spreadsheet shows "#####' in Hours Between Shifts column (D) between Day 1 and 2, and 0:00 in the Hours Between Shifts column (F) between Day 2 and 3. I would like to see a blank in column D and 16:00 hours in column F.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
error handling
0
Answers
1
Hi
One simple solution is to check that there are numbers in both the cells you a subtracting. In cell D11, use this:
=IF(AND(ISNUMBER(E11),ISNUMBER(C12)),E11-C12,"")If you look in the Tutorials section of this site, you'll see Don has several lessons on time calculations. You may be particularly interested in Calculate the Difference Between Time Greater than 24 Hours in Excel
p.s. Suggest you check and correct the spelling of your filename!