Expiry date flag

[Peak Seagull]

Hi,

I am looking for a formula that will return either a Y or N, or an expiry date in column C to remind me to delete a record. The record needs to be deleted after 2 years. Also, I would like the cell to go red when the expiry period has been reached.

Can someone help.

Thanks a lot.

 

[GraH - Guido]

Hi,

I assume the dates in column B are the starting date.
Add 2 years = EDATE([@[Enquiry Date]],24)

Y/N, or TRUE/FALSE: =EDATE([@[Enquiry Date]],24)<=TODAY()

CF to turn red: =$C11 = TRUE

 

[Peter Bartholomew]

Hi Guido
I made some trivial changes to your solution which you may like or hate?
The first is to convert TRUE/FALSE to 1/0 using the SIGN() function.
= SIGN(EDATE([@[Enquiry Date]],24)<=TODAY())
This allows the number format
"Yes";;"No"
to present the result in any way the user requires without losing the underlying significance of the data.
Finally, treat the content of $C11 etc. as a Boolean in its own right, which does not require testing against TRUE or 1.

As I see it, the main argument against the strategy is the extent to which it creates effects by 'smoke and mirrors'.

 

[GraH - Guido]

Hi Guido
I made some trivial changes to your solution which you may like or hate?
The first is to convert TRUE/FALSE to 1/0 using the SIGN() function.

Finally, treat the content of $C11 etc. as a Boolean in its own right, which does not require testing against TRUE or 1.

As I see it, the main argument against the strategy is the extent to which it creates effects by 'smoke and mirrors'.

I like it because I learn a new usage of SIGN.
Clever about CF, though I like it more explicit.
I wanted to show the jobs gets done without hassle. Y/N = 1/0 = TRUE/FALSE. I prefer 1/0 as you can sum them. Smoke and mirrors are quite easily broken by users who actually do not know what's under the hood.

 

[Peak Seagull]

Hi Guido
I made some trivial changes to your solution which you may like or hate?
The first is to convert TRUE/FALSE to 1/0 using the SIGN() function.
= SIGN(EDATE([@[Enquiry Date]],24)<=TODAY())
This allows the number format
"Yes";;"No"
to present the result in any way the user requires without losing the underlying significance of the data.
Finally, treat the content of $C11 etc. as a Boolean in its own right, which does not require testing against TRUE or 1.

As I see it, the main argument against the strategy is the extent to which it creates effects by 'smoke and mirrors'.

Thank you - that's brilliant and just what i was after.