Working With DatesClosed

Question

Hello,

Can someone please help me? I have a list of people who require a licence to drive an FLT. What I want is for excel to display different colours for each persons: past date (red) Current (Green) Next year (Yellow). I know how to get it to display the Red & Green but I'm stuck with they 'Next Year'. I'm trying to make it display yellow if a persons licence takes them into the following year.
The formulas I'm using are:

Default cell colour is Blue for data not entered.
Red is: =AND(B8-TODAY()>=0,B8-TODAY()<=0)
Green is: =AND(B8-TODAY()>=0,B8-TODAY()<=365)
Yellow is: =AND(B8-TODAY()>=0,B8-TODAY()>=365)

Cell B8 would be the test date

rizvisa1 · Answer

Raza, why not use conditional formatting?

First let me confess I am confused about your formula. For example 
AND(B8-TODAY()>=0,B8-TODAY()<=0) , only time i see it happening is when difference is 0 meaning B8 is same as today.

Any ways, if you apply conditional formatting, which is found under Format
you can have 

For date that is less then what is today's date
for condition 1 : Cell value is    --   Less than  --  =Today()   . In Format choose Red Color for pattern

For date that is between today's date and 365 days from today
for condition 2 : Cell value is    --   Between   --  =Today()  AND =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY() )+365) . in format you can choose Green Color for pattern

For date that is more than 365 days from today's date
for condition 3 : Cell value is    --   Greater than  --  =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY() )+365)    . In format you can choose Yellow Color for pattern

Same formula can be used for the way you were trying to do, in case so u wish

Working With Dates

1 response

Similar discussions

Subscribe To Our Newsletter!