Excel people, formula help please!

[sargey]

I have a date in cell A1.

I need a formula that will display the word "Overdue" (in red) in cell A2, if the date in A1 is older that 21 days.

Any help would be great.

 

[The Boosh!]

=IF(A1<TODAY()-21,"overdue","whatever you want")

if you want to take into account weekends/bank hols you will need to use the weekday function which requires the excel analysis toolpack.

 

[richo]

Enter the following in Cell B2 '=TODAY()'

Then enter the date your wanting to compare to in B4 (eg. 01/03/2011)

Then in B5 enter '=IF(DAYS360(B1,B4,1)>21,"OVERDUE","")'

I believe that will work, haven't had chance to test it.

 

[Rhino172]

=IF(A1>TODAY()+21,"overdue","whatever you want")

if you want to take into account weekends/bank hols you will need to use the weekday function which requires the excel analysis toolpack.

Maybe I'm wrong but shouldn't that be -21, not +21, and <, not > as he want's to know if the date is 21 days ago

=IF(A1<TODAY()-21,"overdue","whatever you want")

 

[The Boosh!]

Maybe I'm wrong but shouldn't that be -21, not +21 as he want's to know if the date is 21 days ago

Nope your right mate.

 

[Rhino172]

/geek

 

[Blighty]

Conditional Formatting for the colour scheme

 

[sargey]

Cheers guys.

Sorted!

 

[Brand86]

You can also have in the error message how many days are remaining instead of saying just "OK" or something, which is a bit more usefull...although can someone suggest how you work out how many days left when subtracting a date from a date, sure I've done it before but can't remember how!

I'd also look to add some indicators for conditional formatting, e.g. so anything with 10 days or more left is green, anything betwen 9 - 1 days left is amber, anything 0+ days overdue is red.

EDIT: think this works mate:

=IF(A3<TODAY()-21,"Overdue", SUM(TODAY()-A3))

Where A3 = the date being referenced, this will show you if less than 21 days how many days remaining, or how many days it is in the future between today and that day.

 

[Scutch]

Small g33ks.

/Jealous.

 

[Brand86]

Actually I don't think my last bit does what you want it to do lol, it says how many days after it has expired too! My bad.

 

[The Boosh!]

Actually I don't think my last bit does what you want it to do lol, it says how many days after it has expired too! My bad.

I will vlookup all over your face.

 

[Rhino172]

You can also have in the error message how many days are remaining instead of saying just "OK" or something, which is a bit more usefull...although can someone suggest how you work out how many days left when subtracting a date from a date, sure I've done it before but can't remember how!

I'd also look to add some indicators for conditional formatting, e.g. so anything with 10 days or more left is green, anything betwen 9 - 1 days left is amber, anything 0+ days overdue is red.

EDIT: think this works mate:

=IF(A3<TODAY()-21,"Overdue", SUM(TODAY()-A3))

Where A3 = the date being referenced, this will show you if less than 21 days how many days remaining, or how many days it is in the future between today and that day.

enter this:

=IF(A1<TODAY()-21,"overdue",TODAY()-A1)

Make sure the cell is formatted to number rather than date and it will show you how many days ago the date in A1 is. Example if A1 was 01/04/2011, the result would be 4