Rota time calculations and colour coding

Hi all,

I am currently working on a rota for my work. What I would like to code is that if a shift begins before 8am and finishes after 2pm it automatically deducts an hour for a lunch break. The spreadsheet currently calculates how long a shift is excluding any calculations for breaks, then checks whether that shift is a normal working day for the staff member, and returns the additional hours the staff member worked on that day. Separately, if possible I would like the spreadsheet to colour code each cell. So if a shift starts before 9am the cell fill color to be red, and if a shift begins after 1:30pm for the cell fill color to be dark red. Apologies if this is quite basic, I'm new to Excel.

Many thanks,

Luke

Hi Luke

The easiest way of helping with this is if you would mock up an example workbook with a few permutations of shifts and manually add the results you expect to see and the colours.

In some notes clearly identify which are input cells and which the result cells.

Hi,

Does the attached help. See Sheet1 (2)