Conditional formatting with time
 

Im trying to set up a conditional format for an entire row.
This sheet is used to track arrival times at a specific location. The
location has a scheduled arrival time in column C, and in column D I enter
the actual arrival time, and column E calculates the difference.
I use the 1904 time system so Ill be able to calculate and show negative
time. Column E is set with =IF(D3=€�€�,€�€�,(D3-C3)) this will have column E
appear blank until the actual time is entered.
Without =IF(D3=€�€�,€�€�,(D3-C3)) in column E and with =D3-C3 this will show the
negative of the scheduled time i.e. -4:59 or -18:44. The schedule time is
always present in column C
I would like the conditional format to appear when the actual arrival time
calculation in column E is greater than 0:11 or 0.00763888888888889.
Ive tried =$E30.00763888888888889 & this works when the time was recorded
but if for some reason a arrival time was not recorded or missed, the row is
highlighted.
Also the sheet covers an entire day, and it is not monitored all the time,
so the whole morning section of the sheet will be formatted if no one
recorded arrival times that morning.

Any help is appreciated.

Conditional formatting with time
 

Your text entry "" is being compared with a number, and all text values are
greater than any number.

You could change your =$E30.00763888888888889 to
=AND(ISNUMBER($E3),$E3TIME(,11,)) or
=AND($E3<"",$E3TIME(,11,))
--
David Biddulph

"Dale G" wrote in message
...
I'm trying to set up a conditional format for an entire row.
This sheet is used to track arrival times at a specific location. The
location has a scheduled arrival time in column C, and in column D I enter
the actual arrival time, and column E calculates the difference.
I use the 1904 time system so I'll be able to calculate and show negative
time. Column E is set with =IF(D3="","",(D3-C3)) this will have column E
appear blank until the actual time is entered.
Without =IF(D3="","",(D3-C3)) in column E and with =D3-C3 this will show
the
negative of the scheduled time i.e. -4:59 or -18:44. The schedule time is
always present in column C
I would like the conditional format to appear when the actual arrival time
calculation in column E is greater than 0:11 or 0.00763888888888889.
I've tried =$E30.00763888888888889 & this works when the time was
recorded
but if for some reason a arrival time was not recorded or missed, the row
is
highlighted.
Also the sheet covers an entire day, and it is not monitored all the time,
so the whole morning section of the sheet will be formatted if no one
recorded arrival times that morning.

Any help is appreciated.

Conditional formatting with time
 

Try this as your CF formula:

=AND($E3<"",$E3--"0:11:0")

Hope this helps.

Pete

On Dec 17, 3:56*am, Dale G wrote:
I’m trying to set up a conditional format for an entire row.
This sheet is used to track arrival times at a specific location. The
location has a scheduled arrival time in column C, and in column D I enter
the actual arrival time, and column E calculates the difference.
I use the 1904 time system so I’ll be able to calculate and show negative
time. Column E is set with =IF(D3=””,””,(D3-C3)) this will have column E
appear blank until the actual time is entered.
Without =IF(D3=””,””,(D3-C3)) in column E and with =D3-C3 this will show the
negative of the scheduled time i.e. *-4:59 or -18:44. The schedule time is
always present in column C
I would like the conditional format to appear when the actual arrival time
calculation in column E is greater than 0:11 or 0.00763888888888889.
I’ve tried =$E30.00763888888888889 & this works when the time was recorded
but if for some reason a arrival time was not recorded or missed, the row is
highlighted.
Also the sheet covers an entire day, and it is not monitored all the time,
so the whole morning section of the sheet will be formatted if no one
recorded arrival times that morning.

Any help is appreciated.

Conditional formatting with time
 

David & Pete, Thank you for the help, they all work well.

I'll use this one. =AND($E3<"",$E3--"0:10:59")

"Pete_UK" wrote:

Try this as your CF formula:

=AND($E3<"",$E3--"0:11:0")

Hope this helps.

Pete

On Dec 17, 3:56 am, Dale G wrote:
Im trying to set up a conditional format for an entire row.
This sheet is used to track arrival times at a specific location. The
location has a scheduled arrival time in column C, and in column D I enter
the actual arrival time, and column E calculates the difference.
I use the 1904 time system so Ill be able to calculate and show negative
time. Column E is set with =IF(D3=€�€�,€�€�,(D3-C3)) this will have column E
appear blank until the actual time is entered.
Without =IF(D3=€�€�,€�€�,(D3-C3)) in column E and with =D3-C3 this will show the
negative of the scheduled time i.e. -4:59 or -18:44. The schedule time is
always present in column C
I would like the conditional format to appear when the actual arrival time
calculation in column E is greater than 0:11 or 0.00763888888888889.
Ive tried =$E30.00763888888888889 & this works when the time was recorded
but if for some reason a arrival time was not recorded or missed, the row is
highlighted.
Also the sheet covers an entire day, and it is not monitored all the time,
so the whole morning section of the sheet will be formatted if no one
recorded arrival times that morning.

Any help is appreciated.

.

Conditional formatting with time
 

You're welcome, Dale - thanks for feeding back.

Pete

On Dec 17, 11:54*pm, Dale G wrote:
David & Pete, Thank you for the help, they all work well.

I'll use this one. =AND($E3<"",$E3--"0:10:59")



"Pete_UK" wrote:
Try this as your CF formula:

=AND($E3<"",$E3--"0:11:0")

Hope this helps.

Pete

On Dec 17, 3:56 am, Dale G wrote:
I’m trying to set up a conditional format for an entire row.
This sheet is used to track arrival times at a specific location. The
location has a scheduled arrival time in column C, and in column D I enter
the actual arrival time, and column E calculates the difference.
I use the 1904 time system so I’ll be able to calculate and show negative
time. Column E is set with =IF(D3=””,””,(D3-C3)) this will have column E
appear blank until the actual time is entered.
Without =IF(D3=””,””,(D3-C3)) in column E and with =D3-C3 this will show the
negative of the scheduled time i.e. *-4:59 or -18:44. The schedule time is
always present in column C
I would like the conditional format to appear when the actual arrival time
calculation in column E is greater than 0:11 or 0.00763888888888889.
I’ve tried =$E30.00763888888888889 & this works when the time was recorded
but if for some reason a arrival time was not recorded or missed, the row is
highlighted.
Also the sheet covers an entire day, and it is not monitored all the time,
so the whole morning section of the sheet will be formatted if no one
recorded arrival times that morning.

Any help is appreciated.

.- Hide quoted text -

- Show quoted text -