Playing with dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

johncouzins - 03 Jul 2006 10:54 GMT

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:

Signature

johncouzins

Reply to this Message

Franz Verga - 03 Jul 2006 11:55 GMT

> 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.
[quoted text clipped - 11 lines]
> John
>> eek:

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...).

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

johncouzins - 03 Jul 2006 13:46 GMT

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.

Signature

johncouzins

Reply to this Message

Don Guillett - 03 Jul 2006 15:06 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>
> 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.

Reply to this Message

Don Guillett - 03 Jul 2006 15:50 GMT

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)

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Just change to a:a to 1:1
> =IF(COUNTIF(INDIRECT("A2:A"&COUNTA(1:1)),"<"&DATE(YEAR(C2)-2,MONTH(C2),DAY(C2))),"YES","NO")
>
>> 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.

Reply to this Message

johncouzins - 04 Jul 2006 17:07 GMT

Thank you guys

Reply to this Message

Franz Verga - 03 Jul 2006 15:10 GMT

> 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.

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.

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Rate this thread: