How to calculate Date & Time differences

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

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 your formula to work with conditional formatting. Here are the steps I took to try and implement your suggestion. BTW - I am currently using Excel 2003,

• Selected the cell A2 which has Date & Time information in it that is within an hour of the current time
• Selected Format > Conditional Formatting
• Under Condition 1 I set the drop-down to Formula Is and entered 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 by the SQL database: Oct 4 2005 6:00PM

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

Thank you,
Rob S