Hey there,
I'm trying to put together a rota that will automatically calculate the number of hours worked - subtracting 1 hour for lunch if the staff member works 7 hours or more. However, I need to type "Off" or "A/L" in some of the cells, to signify days off or leave.
Obviously, Excel is unhappy with this and throws back a #VALUE! error because of the text. Just looking for a little help in modifying, or replacing, the formula I'm using in order to achieve this. This current formula calculates for one day:
=IF(MOD(D5-C5,1)*24>=7,MOD(D5-C5,1)*24-1,MOD(D5-C5,1)*24)
The cells containing the times are in 24hr format hh:mm
Thank you in advance for any help you can offer
Josh
I'm trying to put together a rota that will automatically calculate the number of hours worked - subtracting 1 hour for lunch if the staff member works 7 hours or more. However, I need to type "Off" or "A/L" in some of the cells, to signify days off or leave.
Obviously, Excel is unhappy with this and throws back a #VALUE! error because of the text. Just looking for a little help in modifying, or replacing, the formula I'm using in order to achieve this. This current formula calculates for one day:
=IF(MOD(D5-C5,1)*24>=7,MOD(D5-C5,1)*24-1,MOD(D5-C5,1)*24)
The cells containing the times are in 24hr format hh:mm
Thank you in advance for any help you can offer
Josh