Timesheet calculations

[monty_gl]

Hi, I am using Excel for scheduling my stuff and need a special rule which I can't set properly. I believe it will be possible to set it on Excel as it is just a calculation, but don't know how to do it. I have a list with all shifts of an employee for the week and need to check if he had 36 break ( time frame where he has no scheduled hours). I have the starting and the end times of each shift. Can you help me to make this formula, thanks. On the attached file you can see a sample schedule for one person.

[XLent]

can you provide more detail?

do you meant they must have at least one 36 hour interval between their various shifts, or that they must have at least 36 hour gap in total, or something else entirely?

based on your sample file you seem to have 4 gaps, given 5 shifts, and these would equate to gaps of: 23:30; 30:15; 18:00; 22:45

do you agree with the above and, if so, is this a fail?

formula wise, the below would, for ex., derive the 30:15 as max gap between shifts - but it's a little 'hardwired', and not clear if this is what you need...

=MAX(INDEX((C3:C6+H3:H6)-(C2:C5+(I2:I5<H2:H5)+I2:I5);0))

[monty_gl]

Yes, exactly. They must have at least one 36 hour interval between their various shifts, not 36 hours in total. I need a formula with which I can check if the employee has this 36 break once the schedule is ready. Yes, on the sample I attached there is no such break on purpose.

[monty_gl]

Hey Xlent I just tried to reproduce your formula on the sample but seems it is not working properly. Excel says there is no formula and doesn't make any calculations. Any ideas? Attachment 659426

[XLent]

refer attached - highlighted cell simply returning max gap between shifts listed, based on sample data.

in reality you may have more scenarios to account for, and varying number of shifts etc - so you may need to post back with a more detailed sample, along with the expected results.

[monty_gl]

Thank you, XLent! I will take a look into the file and hopefully will get the logic. Next week I will have a sample with more employees and will get back to you. One more question, do you know what I can use to see the gap between the shifts? For example I want to see that the gap between the end time of the shift on 21st and the start time of the shift on 23rd is 18:00 hours, then the gap between the end time of the shift on 23rd and the start time of the shift on 24th etc.

[JeteMc]

To see the individual gaps paste the following into a cell in row 3 and drag down the column to row 6: =SUM(C3,H3)-SUM(C2,I2<H2,I2)
Let us know if you have any questions.

[monty_gl]

Thanks for the update, JeteMc! I tried to use your formula but it seems it doesn't work properly. It doesn't calculate the gap properly, only showing strange numbers. For example the gap between the first two shifts is 0.98 etc. I tried to format the cell but no success.

[XLent]

in XL time is decimal - i.e. 1 = 24 hours, and 0.5 = 12 hours

given your potential results exceed 24 hours apply a custom format of [hh]:mm

[monty_gl]

I've got it, thanks.

[JeteMc]

Thank You for the feedback. If the issue is resolved, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.