Work rota as spreadsheet

Question

Level 1

4 points

Work rota as spreadsheet

Hi, hope I can explain this properly.

i want to create a sheet that shows my work rota

the rota is 5 weeks long and just repeats indefinatly. I work day shifts of 9h 23m and night shifts of 11h 15m..

as a visual, I just want the cell to say Day or Night but I would like my weekly hours totalled at the end. Now I know how to sum durations in apple numbers but how can I make it recognise in the background that if the cell contains “Day” or “night”, then the value of that cell behind the scenes should equal 9h 23mor 11h 15m?

Any help would be great

Posted on Sep 2, 2024 3:43 AM

Reply

Answer 1

Yellowbox

Level 7

21,209 points

Sep 2, 2024 5:11 AM in response to jaylockwood

Hi Jay,

Try this.

Formula in C2 is IF(B2="","",IF(B2="Day",'Day/Night'::B$1,'Day/Night'::B$2))

If a value in B is blank ("", NULL) because you didn't work that night or day, then insert ""

Else insert the Duration from the helper table (Day/Night) on the right.

The helper table will be a handy way to adapt if the Durations of your shifts change.

Please call back with questions.

Regards,

Ian.

Reply