Calculate 60 and 90 day dates

[CorieB]

Hello! I am trying to get a formula to calculate when a 60 and 90 day limit is reached. I have 3 columns: one with an start date, one with a formula for 90 days from that start date, and another with the date the item was actually shipped. I need to formulate the second column to turn yellow when the date in it is 60 days from the first column and turn red when it is 90 days from the first column. I have already calculated the second column to turn green when there is a ship date in the third column, but I am having an issue with that one too. For some reason it is skipping a couple of rows and I cannot see any reason why.

Any help would be appreciated! Thank you so much.

[etaf]

you need to make sure the range you select and the formula match

Ie if the range is A5:E200
then the formula needs to be A5

Assuning the data starts
A2, B2, C2
Start Date , 90 Days Shipped

So you can use the formula in 90 days column for the RED

=A2 > today() -60

otherwise

Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[CorieB]

Thank you, but I do not think I explained myself very well...let me try again (I am not very good at this)...

I need the 60 days to be from the date in the second column, not today's date, would I use the same formula?

Basically, I have waste that needs to be disposed of within 90 days of the accumulation start date (column B). Column C is formulated to show 90 days from the accumulation start date (column B). I need to change the color of column C to reflect when the waste is getting close to the need to ship date (90 days). So I want to formulate it to turn yellow at 60 days from accumulation date and red at 90 days from accumulation date....

[etaf]

accumulation date + 60 is 60 days from that date

but dont you want to compare to another date to turn yellow like todays() date

lets say
accumulation date is
1/jan/16
so plus 60days is
1/mar/16

so to turn yellow - doesn't the spreadsheet need to know todays() date to say if we are over the 60 days or not?
and if it is turn yellow

so A2 + 60 > today()

which means if today() is passed the 1/3/16
so turn yellow
if not dont change colour

[CorieB]

Ohhh I see what you are saying now. So I need Column C to be green if column D is filled in with a date, yellow if at 60 days, and red if at 90 days. How do I add all three?

[etaf]

three rules in conditional formatting

Yellow
= A2 + 60 > today()
Red
= A2 + 90 > today()
Green
= D2 <>""

But the range you select depends on what row you want to turn green

For Green
Select C:C
as you have selected the whole column you have to start at row 1
=D1=""
NOW that will work OK
BUT had you selected
C:D
you would need a $ to stop the column moving 1 column with the Range
=$D1=""

otherwise
attach the sample as suggested

[CorieB]

I have attached an excel file. I placed everything right where it is on my actual spreadsheet.

[etaf]

do you want to add some dates and the colour you want
with examples for all colours please

[CorieB]

It did not save the file when I changed it...let me change it again...

[CorieB]

Here is the updated sheet. Sorry about that!

[Phuocam]

Select C:C

Formula for CF

Green -> =AND($D3<>"",$D3-$B3<=60)

Yellow -> =AND($D3-$B3>60,$D3-$B3<=90)

Red -> =$D3-$B3>90