Rotation schedule formula

[sv29]

Hello

I have a file that creates a work rotation schedule, based on the rotation, hours worked per day, and start and end dates, and the file/formula works great, however it assumes the same amount of hours worked per day. I need to add in the capabilities to change the hours worked per day, specifically the travel in (day before the rotation starts, and the day after the rotation finishes).

Please see attached file, the top table is the current table I use formulas in the dates range, I would like to adjust this formula to include the travel days/hours in and out as per the bottom table example.

Any help would be greatly appreciated

Thanks

 

[AliGW]

Still using Excel 2016?

 

[sv29]

Still using Excel 2016?

I don't think its 2016, I have the Microsoft Excel for Microsoft 365 MSO so it should be the latest version

 

[AliGW]

You need to update your forum profile to 365, then, as it currently says 2016.

Try this in M14 copied across and down:

=LET(r,M$13,
m,MOD(r-$H14,$B14)+1,
IF((r>=$H14-1)*(r<=$I14+1),IF(m=$B14,$F14,IF(m=$C14+1,$G14,IF(m<$C14+1,$E14,""))),""))