Help with IF Statements...

[Nicola1]

Hi All,
Please could you help - I have got myself in a muddle with my IF statements.

In cell A1 I have an end date. If the date in A1 is more than 21 days ago from today then I was B1 to return "Archive". If A1 is blank I want it to leave cell B1 blank.

Eg: A1 17/11/2017 B1 "blank"
A2 04/08/2017 B1 Archive

Thank you

 

[MrTeeny]

=IF(AND(TODAY()-A1>=21,A1<>""),"ARCHIVE","")

Today is the 25/08/2017 in the UK so 4/08/2017 is exactly 21 days, remove the equals sign if it's 26/08/2017 wherever you're based.

 

[Nicola1]

That's amazing thank you so much! Its worked perfectly! I've been playing about for hours, I never would have got that.

Would you mind talking me through the formula? So I can apply the same principle in future

Thanks again

 

[MrTeeny]

Yep no problem the code breaks down to

IF(

AND(TODAY()-A1>=21,A1<>""),

"ARCHIVE"

,"")

So if AND(TODAY()-A1>=21,A1<>"") is true we show ARCHIVE, if it's false we show "" i.e. nothing

The AND formula just allows us to use more than one condition and both have to be true for that the formula to be true.

The AND statement has two parts split by a comma.

AND(

TODAY()-A1 >=21

A1<>""

)

The part TODAY() just returns whatever today's date is i.e. 25/08/2017, so TODAY()-A1 is today's date less the value in A1, say 01/08/2017, excel recognises these are dates so works out the number of days for us, in this case 24. We are just comparing that to be greater than or equal to 21 (>=21) so that part of the AND statement is TRUE, 24 is greater than 21. The second part A1<>"" is basically there to trap errors because if we had nothing in A1 then TODAY() less nothing would be higher than 21 so we need to trap those errors by only returning true if A1 has something in it i.e. A1<>"", A1 is not equal to nothing, the greater than and less than signs together (<>) mean not equal to .


Hope that makes it a bit clearer if you're still non the wiser just ask.

 

[Nicola1]

Thank you so much MrTeeny. That is really helpful thank you, I really appreciate it. I now can see where I was going wrong