Looking For A Time Left Calculator That Automatically Color Codes A Cell

[shalorian20]

Alright, I know that title was weird. Here's what I'm trying to do:

Let's say I have someone who is in training from 12 JUL to 01 NOV and I want to setup a function to automatically calculate the total calendar days they are gone and then COLOR CODE a cell for time remaining.

For example:
120 Days = RED
90 Days = ORANGE
60 Days = YELLOW
30 Days = GREEN

But! I want this color coded cell to automatically update with each day that passes and change the color appropriately as well as displaying the appropriate and correct number of days remaining?

I was looking at the Conditional Formatting rules that were linked to me in another thread, but didn't really find what I was looking for (or if i did it went right over my head).

Any ideas?

[Pete_UK]

You can get the number of days remaining by using this formula:

=DATE(2013,11,1) - TODAY()

and formatting that cell as General.

Then you can apply conditional formatting to that cell - choose the option to "Use a formula ... etc." and then set up 4 different conditions as follows:

=cell<=30 format Green
=AND(cell<=60,cell>30) format Yellow
=AND(cell<=90,cell>60) format Orange
=cell>90 format RED

you need to put the appropriate cell reference in the formulae instead of cell.

Hope this helps.

Pete

[mike65535]

It's probably easiest conceptually to create a series of cells that calculates the number of days remaining

Col A has start, Col B has end and column C is the remaining time formula

=IF(TODAY()>A2, B2-TODAY(), B2-A2)

Then apply a conditional format to the cells that test the value for your red/orange/yellow/green

[mike65535]

Something like this? (Excel 2007)

Book1.xlsx

[shalorian20]

Something like this? (Excel 2007)

Attachment 252727

Yes very similar! my only issue is, everyone on the list isn't gone for training so i want to be able to leave some field blank. when i do this, i get -41479 in the Time Remaining cell

I'm working on setting up the condition right now for them too

[shalorian20]

You can get the number of days remaining by using this formula:

=DATE(2013,11,1) - TODAY()

and formatting that cell as General.

Then you can apply conditional formatting to that cell - choose the option to "Use a formula ... etc." and then set up 4 different conditions as follows:

=cell<=30 format Green
=AND(cell<=60,cell>30) format Yellow
=AND(cell<=90,cell>60) format Orange
=cell>90 format RED

you need to put the appropriate cell reference in the formulae instead of cell.

Hope this helps.

Pete

for some reason, I can't get this conditioning to work. not sure where i'm going wrong with it either

[shalorian20]

here's what i have for the formulas right now:

=IF(TODAY()>D2,E2-TODAY(),E2-D2)

^---all the way to D29:E29 for that formatting

Conditioning Formatting:
=F2:F29<=30
=AND(F2:F29<=60,F2:F29>30)
=AND(F2:F29<=90,F2:F29>60)
=AND(F2:F29>90)
with the right colors formatted. for some reason, just not working ><

[Pete_UK]

When you are setting up the conditional formatting you should select all the cells that it will apply to first (i.e. F2 to F29) and then click on Conditional Formatting | New Rule | Use a formula ..., and then in your formula you should refer to the active cell in your range (i.e. F2) and when you come out of the dialogue box then Excel will automatically adjust the cell references to suit your range. So, your formulae should be:

CF 1: =F2<=30
CF 2: =AND(F2<=60,F2>30)
CF 3: =AND(F2<=90,F2>60)
CF 4: =F2>90

Hope this helps.

Pete

[shalorian20]

When you are setting up the conditional formatting you should select all the cells that it will apply to first (i.e. F2 to F29) and then click on Conditional Formatting | New Rule | Use a formula ..., and then in your formula you should refer to the active cell in your range (i.e. F2) and when you come out of the dialogue box then Excel will automatically adjust the cell references to suit your range. So, your formulae should be:

CF 1: =F2<=30
CF 2: =AND(F2<=60,F2>30)
CF 3: =AND(F2<=90,F2>60)
CF 4: =F2>90

Hope this helps.

Pete

yes! that was precisely what i was doing wrong! thank you so much!

now the only thing i have left to ask help for is this:

START DATE = [BLANK]
End Date = [BLANK]
Time Remaining = -41479

for some reason, when i leave those two field blanks i get that strange number display. i've been trying different options to get it to just display nothing but an empty cell, but all for naught.

ideas?

[Pete_UK]

What formula do you have in there at the moment?

Pete

[Pete_UK]

Ah, if it is this one:

=IF(TODAY()>D2,E2-TODAY(),E2-D2)

(from post #7), then you should do this:

=IF(OR(D2="",E2="),"",IF(TODAY()>D2,E2-TODAY(),E2-D2))

i.e. if either D2 or E2 is blank then show a blank, otherwise apply your formula.

Hope this helps.

Pete

[shalorian20]

Ah, if it is this one:

=IF(TODAY()>D2,E2-TODAY(),E2-D2)

(from post #7), then you should do this:

=IF(OR(D2="",E2="),"",IF(TODAY()>D2,E2-TODAY(),E2-D2))

i.e. if either D2 or E2 is blank then show a blank, otherwise apply your formula.

Hope this helps.

Pete

Pete, you nailed it. That was the last step stopping me from calling this project complete.

Thank you all so much for putting up with my newbishness and helping me through this