Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
robs
 
Posts: n/a
Default 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

  #2   Report Post  
Roger Govier
 
Posts: n/a
Default

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




  #3   Report Post  
robs
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
date & time rob Excel Discussion (Misc queries) 1 September 19th 05 10:54 PM
How do I calculate if a date is in a certain time frame? Pe66les Excel Worksheet Functions 19 August 27th 05 11:07 PM
Calculate time Rao Ratan Singh Excel Discussion (Misc queries) 2 March 17th 05 03:28 PM
Automatically enter date and time but only update once. PM New Users to Excel 3 January 21st 05 08:47 AM
calculating date time ranges Patrick Excel Worksheet Functions 2 November 11th 04 06:07 AM


All times are GMT +1. The time now is 10:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"