Playing with dates

J

johncouzins

Rite here I go, I want to automatically add today’s date in a cell,
which I have not done in so long I have forgotten.

Then from using this date I want to be able to search an entire row of
dates and find out if any dates in the row are older then 2 years, if
there is any date that is older then 2 year for it to say yes or
anything.

Please can someone help me this has been battering my head for days
now?


Cheers


John
:eek:
 
F

Franz Verga

johncouzins said:
Rite here I go, I want to automatically add today's date in a cell,
which I have not done in so long I have forgotten.

Then from using this date I want to be able to search an entire row of
dates and find out if any dates in the row are older then 2 years, if
there is any date that is older then 2 year for it to say yes or
anything.

Please can someone help me this has been battering my head for days
now?


Cheers


John
Click to expand...


Hi John,

For today date you can use the TODAY function.

To have YES if a date is older then 2 year from today you can use this
formula:

=IF(COUNTIF(INDIRECT("A2:A"&COUNTA(A:A)),"<"&DATE(YEAR(C2)-2,MONTH(C2),DAY(C2))),"YES","NO")

where you have today date in C2 and the dates to check are in column A (I
set a dynamic range...).


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
J

johncouzins

That does exactly what i want it to cheers mate, but i cant seem to get
it to work accross a row say only to include the cell range A5:D5.
Sorry if its me being stupid.
 
F

Franz Verga

johncouzins said:
That does exactly what i want it to cheers mate, but i cant seem to
get it to work accross a row say only to include the cell range A5:D5.
Sorry if its me being stupid.
Click to expand...


If your dates are across a row, you can use this version af the previous
formula:

=IF(COUNTIF(INDIRECT("A5:"&ADDRESS(5,COUNTA(5:5))),"<"&DATE(YEAR(A15)-2,MONTH(A15),DAY(A15))),"YES","NO")


Also in this version the rabge is dynamic. The assumption are:
- dates are in row 5, starting from A5;
- there is nothing but the dates in row 5.

To change this assumpiton you have to modify this part of the formula:

INDIRECT("A5:"&ADDRESS(5,COUNTA(5:5)))

You can also change to a staic range replacing the above with your stic
range.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
D

Don Guillett

my bad but if you want a cell below to indicate why not just

=IF(a2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())),1,2)
 
Top