Recalculation of Formula.

[Big Rick]

Hello everybody.
In cell F5 I have the formula '=(D5-C5-E5)*1440/60.
This formula will give me the duration of a work shift.
( D5 = Finish Time C5 = Start Time E5 = Lunch Break.)

In a seperate cell (Say H5) I want to put in general information, (Day off,
Late Shift, etc.) but if that information is "Sick day", I want F5 to
display 7.50 (as in hours) without any start or finish times in the other
cells.

Any help would be greatly appreciated.


Thanking you in anticipation

 

[Ian]

In F5: =IF(H5="Sick day",TIME(7,30,0),=(D5-C5-E5)*1440/60)

 

[Big Rick]

Thanks but I am getting an error when typing this in.
Please can you help further

 

[Big Rick]

I have tweaked Ian's formula (many thanks to him) to read
=IF(H5="sick day",Holidays!V4/5,(D5-C5-E5)*1440/60)

Holidays!V4 contains the number 37.50 or 40. This is for different contracts.
This now works correctly.

However,could you help me one stage further.
If H5 does contain the words "sick day", I would like the result of the
formula (either 7.50 or 8) to be in red. I would normally conditionally
format, but cannot do this because it is possible to actually work a 7.5hr or
8hr day and these cannot be in red.

Thanking you in anticipation again.

 

[Ian]

You can still use conditional formatting. In F5 select conditional
formatting, select Formula is and enter =H5="Sick day". Then select the
formatting you require.

 

[Big Rick]

Many thanks to Ian for all his help. I have never been let down by the help
from you all.