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
--
Big Rick

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

--
Ian
--
"Big Rick" wrote in message
...
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
--
Big Rick

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


"Ian" wrote:

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

--
Ian
--
"Big Rick" wrote in message
...
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
--
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.
--
Big Rick


"Big Rick" wrote:

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


"Ian" wrote:

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

--
Ian
--
"Big Rick" wrote in message
...
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
--
Big Rick

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.

--
Ian
--
"Big Rick" wrote in message
...
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.
--
Big Rick


"Big Rick" wrote:

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


"Ian" wrote:

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

--
Ian
--
"Big Rick" wrote in message
...
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
--
Big Rick

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


"Ian" wrote:

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.

--
Ian
--
"Big Rick" wrote in message
...
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.
--
Big Rick


"Big Rick" wrote:

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


"Ian" wrote:

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

--
Ian
--
"Big Rick" wrote in message
...
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
--
Big Rick