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

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

Jerry

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 Jerry,

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 =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 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

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 wrote:

> Hello Jerry,
>
> 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 *=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 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

Hello Jerry,

Thanks for all your help on this. You are correct, the field being populated from SQL was writing as TEXT instead of an Excel Date/Time value. I have since changed the way SQL outputs to the XLS spreadsheet and the formula is now working correctly. The cell turns YELLOW when 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 use 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 it is equal to or past the current time? If you know of a better way of doing this, please let me know.

Thank you,
Rob S

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 wrote:

> Hello Jerry,
>
> Thanks for all your help on this. You are correct, the field being
> populated from SQL was writing as TEXT instead of an Excel Date/Time
> value. I have since changed the way SQL outputs to the XLS spreadsheet
> and the formula is now working correctly. The cell turns YELLOW when
> 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 use
> 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 it
> is equal to or past the current time? If you know of a better way of
> doing this, please let me know.
>
> Thank you,
> Rob S
>
>
>

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

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

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

Jerry

robs wrote:

> 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