Alter numeric values by size

[lengjay]

Hi.

Apologies in advance for the novice nature of the question!

I'm currently trying to create a rota timesheet which automatically takes shift patterns and deducts for unpaid breaks.

Basically if a shift is less than 4.5 hours long then no break is deducted
If the shift length is 4.5 - 6.5 hours then 15 mins are deducted
If the shift is longer than 6.5 hours then 30 mins are deducted

I've left the file as an attachment - if you can suggest a better way to code such a program I would also be grateful.

Thanks in advance for any help/advice you can spare.

Jason.

[darkyam]

Try

Please Login or Register  to view this content.

in F2. You can get rid of columns D and E and the matrix tab then.

[teylyn]

This looks ugly, but it works

example formula for Mel's shift in F22

=C22-B22-LOOKUP(C22-B22,{0,0.1875,0.270833},{0,0.0104166666666667,0.0208333333333333})

Format as h:mm to get time format or multiply by 24 to get decimal hours.

[shg]

Or to get rid of all the decimals, =C2-B2 - LOOKUP(C2-B2, {0,4.5,6.5} / 24, {0,15,30} / 1440)

[lengjay]

Really really thank you for all your help guys! Slowly getting there!

<SOLVED>