Please help!

I have created an Excel spreadsheet that tracks items from a SQ
database. One of the things I need to keep track of is when one of th
cells in the Date & Time column is within one hour from the curren
time. I know you can setup conditional formating to change the cel
color. However, what I am looking for is some type of formula tha
will take data from the Date & Time colum and subtract the curren
time. If the result is less than 1 hour I would like the cell with th
scheduled date to turn Yellow.

Below is an example of the results I am looking for. If possible
would like to have the entire formula in conditional formating so tha
I do not have to have the current time shown on the spreadsheet.

Date & Time Current Time Result
10/03/05 01:00PM 10/03/05 11:00AM No Action
10/03/05 01:00PM 10/03/05 12:05PM Date & Time cell turn
Yello

Thanks for the help!
Rob

--
rob
-----------------------------------------------------------------------
robs's Profile: http://www.excelforum.com/member.php...nfo&userid=409
View this thread: http://www.excelforum.com/showthread.php?threadid=47287

Hi Robs

With your Date & Time in A2, set Format>Conditional Formatting>select
the drop down for Formula Is and in the white pane type
=AND(A2>NOW(),A2-NOW()<TIME(1,0,0))
Set Format>Patterns>Yellow

Use the format painter to copy the formatting to whatever range of cells
you require.

Regards

Roger Govier



robs wrote:

>Please help!
>
>I have created an Excel spreadsheet that tracks items from a SQL
>database. One of the things I need to keep track of is when one of the
>cells in the Date & Time column is within one hour from the current
>time. I know you can setup conditional formating to change the cell
>color. However, what I am looking for is some type of formula that
>will take data from the Date & Time colum and subtract the current
>time. If the result is less than 1 hour I would like the cell with the
>scheduled date to turn Yellow.
>
>Below is an example of the results I am looking for. If possible I
>would like to have the entire formula in conditional formating so that
>I do not have to have the current time shown on the spreadsheet.
>
>Date & Time Current Time Result
>10/03/05 01:00PM 10/03/05 11:00AM No Action
>10/03/05 01:00PM 10/03/05 12:05PM Date & Time cell turns
>Yello
>
>Thanks for the help!
>Robs
>
>
>
>

Hello Roger,

Thanks for the information below, however I am unable to get you
formula to work with conditional formatting. Here are the steps I too
to try and implement your suggestion. BTW - I am currently using Exce
2003,


- Selected the cell A2 which has Date & Time information in it tha
is within an hour of the current time
- Selected *Format* > *Conditional Formatting*
- Under Condition 1 I set the drop-down to *Formula Is* and entere
the formula *=AND(A2>NOW(),A2-NOW()<TIME(1,0,0))*
- Clicked *Format* within the dialog box and selected *Paterns*
*Yellow*


Here is a sample of the Date & Time format that is being produced b
the SQL database: Oct 4 2005 6:00PM

Please let me know if there are any steps I am missing in order to hav
conditional formatting monitor for Dates & Times that are within 1 hou
of the current time.

Thank you,
Rob

--
rob
-----------------------------------------------------------------------
robs's Profile: http://www.excelforum.com/member.php...nfo&userid=409
View this thread: http://www.excelforum.com/showthread.php?threadid=47287