How to calculate Date & Time differences

[robs]

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

 

[Jerry W. Lewis]

Assuming that you have Excel 97 or later, use a conditional format based
on the formula
=ABS(dte-cur)<1/24

Jerry

 

[robs]

Hello Jerry,

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 *=ABS(dte-cur)<1/24*
- 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

 

[Jerry W. Lewis]

Your description is a bit sketchy. Open the conditional formatting
dialog; did Excel put quote marks around the formula? If so, then
remove them and prefix the formula with an equals sign.

What was the exact formula that you put in? I used dte and cur as place
holders, since you didn't say where the data was coming from. Did you
replace dte with A2? Did you replace cur with an appropriate cell
reference or with NOW()?

Try copying the formula from the conditional formatting dialog and
putting it into a cell. What does it return?

I assumed that A2 was an Excel date/time value. Does =ISNUMBER(A2)
return TRUE, or is A2 in fact text?

Jerry

 

[robs]

Hello Jerry,

Thanks for all your help on this. You are correct, the field bein
populated from SQL was writing as TEXT instead of an Excel Date/Tim
value. I have since changed the way SQL outputs to the XLS spreadshee
and the formula is now working correctly. The cell turns YELLOW whe
the time in the cell is within one hour of the current time.

Now to make things a little more complicated. Is there a way to us
conditional formatting to make the cell turn YELLOW when its date
time are within one hour of the current time and then turn RED when i
is equal to or past the current time? If you know of a better way o
doing this, please let me know.

Thank you,
Rob

 

[Jerry W. Lewis]

On the conditional formatting dialog, there is an "Add" button that
allows you to have up to 3 conditions, each with its own conditional
format. Conditions are evaluated in order from 1 to 3 until one is
satisfied.

=A2-NOW()>=0
tests for whether A2 is equal to or past the current time. If the cell
to be formatted is A2, then you could alternately use "Cell Value Is"
"greater than or equal to" instead of "Formula Is"

Jerry

 

[robs]

Hello Jerry,

Thanks again for all of your help. Things are all setup now and seem
to be working well. Thank you for taking the time to help me with
this.

Regards,
Rob S

 

[robs]

Hello once again Jerry,

I may have spoken a little too soon. The issue I am having now is that
the data is Dynamically from SQL on an auto-refresh interval of 15
minutes. I have setup my conditional formating as follows:

Condition 1 - =A2-NOW()<=0 (Cell Turns Red in color)
Condition 2 - =ABS(A2-NOW())<1/24 (Cell Turns Yellow in color)

The issue I am having now is that any BLANK cells that have that
conditional formating applied to them turn RED in color. Is there a
way to add a third condition so that BLANK cells do not change color
when the two conditions above are applied?

Thank you,
Rob S

 

[Jerry W. Lewis]

Use
=ISNUMBER(A2)*(A2-NOW()<=0)
=ISNUMBER(A2)*(ABS(A2-NOW())<1/24)
for your two conditions.

Jerry