Conditional Formatting for Date/Time differential

K

Keeper4826

New Member
Joined
Nov 6, 2006
Messages
47
I am trying to track reports which need to be submitted within a certain time frame. I have a time hack, which is when the clock starts. This cell is formatted as dd/mm/yy hh:mm (24hr). The next cell is time submitted, using the same format. The target time for submission is within 8hrs of the time hack. I’m like to use conditional formatting to alert me to reports yet to be submitted.

• If the cell is blank (no report submitted) and the current date/time is less than or equal to 8hrs, the cell will be yellow.
• If the cell is blank (no report submitted) and the current date/time is great than 8hrs, the cell will be red.
• If the difference between the value of Time Submitted and Time Hack is less than or equal to 8hrs, the cell will be green.
• If the difference between the value of Time Submitted and Time Hack is greater than 8hrs, the cell will be orange.

I cannot figure out the rules for these formatting criteria. I know how to calculate the difference between two values, so long as the submission time is within 24hrs. Beyond that my formula fails. If there is no submission, the formula obviously fails too. I thought I’d use this extra data as the basis for my tracking rather than using just the two original columns. The conditional formatting doesn’t take the results of the original formula in the cell. How can I configure the conditional formatting? I am using Excel 2003.

Here is example data:
Time Hack: 9/6/09 12:00
Submission Time: 9/6/09 19:30
Submission Time: 9/6/09 20:00
Submission Time: 9/6/09 20:30
Submission Time: 9/7/09 14:00
Submission Time: blank
 
Sort by date Sort by votes
[TABLE="class: MsoNormalTable, width: 259"]
<tbody>[TR]
[TD="width: 181, bgcolor: transparent"] Time Hack<o:p></o:p>
[/TD]
[TD="width: 165, bgcolor: transparent"] T - Submitted<o:p></o:p>
[/TD]
[TD="width: 86, bgcolor: transparent"] REPORT<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"]
9/6/2009 12:00<o:p></o:p>​
[/TD]
[TD="width: 165, bgcolor: transparent"]
9/6/2009 19:30<o:p></o:p>​
[/TD]
[TD="width: 86"]
7.50<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/6/2009 20:00<o:p></o:p>​
[/TD]
[TD="width: 86"]
8.00<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/6/2009 20:30<o:p></o:p>​
[/TD]
[TD="width: 86"]
8.50<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/7/2009 14:00<o:p></o:p>​
[/TD]
[TD="width: 86"]
26.00<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/8/2009 14:00<o:p></o:p>​
[/TD]
[TD="width: 86"]
50.00<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/6/2009 14:30<o:p></o:p>​
[/TD]
[TD="width: 86, bgcolor: transparent"]
2.50<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"][/TD]
[TD="width: 86, bgcolor: transparent"]
0.00<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"][/TD]
[TD="width: 86, bgcolor: transparent"]
0.00<o:p></o:p>​
[/TD]
[/TR]
</tbody>[/TABLE]
THISWAS DONE IN V-2007, but it may help youto set up your V-2003, I believe that your version for conditional formattingis restricted to only three conditions but the processes are basically thesame.<o:p></o:p>
Setreport column format to number and use this formula: =IF($B2<1,0,($B2-$A2)*24) BE SURE THEFORMULA IS DIRECTED TO THE PROPER CELLS!! My columns are “A”,”B”,AND “C”. Thenuse the cond. Format function in ribbon to highlight Color and put parameter incells. Click on Cond. Format choose “highlight cells rules”, then to “morerules” at bottom click on “only cells that contain…” First window choose “cellvalue” Second window choose “less than” in third enter =7.00 . Then click on“Format” a color pallet will appear choose a clear / white color or “No Color”and click on OK. Then apply and OK again. Stay in that same cell and put thenext time submitted < 8 go to cond. format and follow previous instructionbut window two choose between and the last>8.6 choose greater than. Stay inthat cell until you have set-up the color and parameters for each condition.Parameters should be (1.) <7,No Color, (2.) Between 7.1 and 7.6 Yellow, (3.)Btw 7.6 and 8.1 Red, (4.) Btw 8.1 and 8.6 Green, (5.) > 8.6 Orange for 2007.You use for 2003 the best parameters that will give you a better idea of whatis happening. After you have the formula and all the colors and parameters setin the first cell, then copy that cell down the column or double click the fillhandle. As long as you have data in the column to the left double clicking thefill handle will copy all the way to the bottom of your data and the colorswill change as the parameters change.<o:p></o:p>
<o:p> </o:p>
 
Upvote 0
[TABLE="class: MsoNormalTable, width: 259"]
<tbody>[TR]
[TD="width: 181, bgcolor: transparent"]Time Hack<o:p></o:p>
[/TD]
[TD="width: 165, bgcolor: transparent"]T - Submitted<o:p></o:p>
[/TD]
[TD="width: 86, bgcolor: transparent"]REPORT<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"]
9/6/2009 12:00<o:p></o:p>​
[/TD]
[TD="width: 165, bgcolor: transparent"]
9/6/2009 19:30<o:p></o:p>​
[/TD]
[TD="width: 86"]
7.50<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/6/2009 20:00<o:p></o:p>​
[/TD]
[TD="width: 86"]
8.00<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/6/2009 20:30<o:p></o:p>​
[/TD]
[TD="width: 86"]
8.50<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/7/2009 14:00<o:p></o:p>​
[/TD]
[TD="width: 86"]
26.00<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/8/2009 14:00<o:p></o:p>​
[/TD]
[TD="width: 86"]
50.00<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"]
9/6/2009 14:30<o:p></o:p>​
[/TD]
[TD="width: 86, bgcolor: transparent"]
2.50<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"][/TD]
[TD="width: 86, bgcolor: transparent"]
0.00<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 181, bgcolor: transparent"][/TD]
[TD="width: 165, bgcolor: transparent"][/TD]
[TD="width: 86, bgcolor: transparent"]
0.00<o:p></o:p>​
[/TD]
[/TR]
</tbody>[/TABLE]
THISWAS DONE IN V-2007, but it may help youto set up your V-2003, I believe that your version for conditional formatting is restricted to only three conditions but the processes are basically the same. Also the colors didn't show on this example but it works fine for me... follow the similar steps below using 2003 excel. <o:p></o:p>
Setreport column format to number and use this formula: =IF($B2<1,0,($B2-$A2)*24) BE SURE THEFORMULA IS DIRECTED TO THE PROPER CELLS!! My columns are “A”,”B”,AND “C”. Thenuse the cond. Format function in ribbon to highlight Color and put parameter incells. Click on Cond. Format choose “highlight cells rules”, then to “morerules” at bottom click on “only cells that contain…” First window choose “cellvalue” Second window choose “less than” in third enter =7.00 . Then click on“Format” a color pallet will appear choose a clear / white color or “No Color”and click on OK. Then apply and OK again. Stay in that same cell and put thenext time submitted < 8 go to cond. format and follow previous instructionbut window two choose between and the last>8.6 choose greater than. Stay inthat cell until you have set-up the color and parameters for each condition.Parameters should be (1.) <7,No Color, (2.) Between 7.1 and 7.6 Yellow, (3.)Btw 7.6 and 8.1 Red, (4.) Btw 8.1 and 8.6 Green, (5.) > 8.6 Orange for 2007.You use for 2003 the best parameters that will give you a better idea of whatis happening. After you have the formula and all the colors and parameters setin the first cell, then copy that cell down the column or double click the fillhandle. As long as you have data in the column to the left double clicking thefill handle will copy all the way to the bottom of your data and the colorswill change as the parameters change.<o:p></o:p>
<o:p></o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
If a cell contains the word "Completed", then return the date/time when it changed.
2
Replies
14
Views
1K
dreid1011
dreid1011
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
374
Madam_Xavier
M
E
  • Question Question
Negative times and conditional formating.
Replies
2
Views
671
Ludwig
L
C
  • Solved
Conditional Formatting of Months in a Date Range
Replies
5
Views
260
Peter_SSs
Peter_SSs
K
  • Solved
Two dates, two cells - How do I format the second cell to change colour after x amount of time has elapsed from the first date?
Replies
8
Views
338
Kikaiki
K

Forum statistics

Threads
1,226,840
Messages
6,193,281
Members
453,788
Latest member
drcharle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top