Conditional Formatting for Date/Time differential

[Keeper4826]

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

 

[bsmill2]

Time Hack<o></o>	T - Submitted<o></o>	REPORT<o></o>
9/6/2009 12:00<o></o>​	9/6/2009 19:30<o></o>​	7.50<o></o>​
	9/6/2009 20:00<o></o>​	8.00<o></o>​
	9/6/2009 20:30<o></o>​	8.50<o></o>​
	9/7/2009 14:00<o></o>​	26.00<o></o>​
	9/8/2009 14:00<o></o>​	50.00<o></o>​
	9/6/2009 14:30<o></o>​	2.50<o></o>​
		0.00<o></o>​
		0.00<o></o>​

<tbody> </tbody>

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></o>
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></o>
<o> </o>

 

[bsmill2]

Time Hack<o></o>	T - Submitted<o></o>	REPORT<o></o>
9/6/2009 12:00<o></o>​	9/6/2009 19:30<o></o>​	7.50<o></o>​
	9/6/2009 20:00<o></o>​	8.00<o></o>​
	9/6/2009 20:30<o></o>​	8.50<o></o>​
	9/7/2009 14:00<o></o>​	26.00<o></o>​
	9/8/2009 14:00<o></o>​	50.00<o></o>​
	9/6/2009 14:30<o></o>​	2.50<o></o>​
		0.00<o></o>​
		0.00<o></o>​

<tbody>
</tbody>

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></o>
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></o>
<o></o>