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


--
robs
------------------------------------------------------------------------
robs's Profile: http://www.excelforum.com/member.php...fo&userid=4098
View this thread: http://www.excelforum.com/showthread...hreadid=472878

Hi Robs

With your Date & Time in A2, set FormatConditional Formattingselect
the drop down for Formula Is and in the white pane type
=AND(A2NOW(),A2-NOW()<TIME(1,0,0))
Set FormatPatternsYellow

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(A2NOW(),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


--
robs
------------------------------------------------------------------------
robs's Profile: http://www.excelforum.com/member.php...fo&userid=4098
View this thread: http://www.excelforum.com/showthread...hreadid=472878