Number of days left shown???

[Guest]

Hi there...
I would like to ask if there;s any formula for this??

I have a spreadsheet with names and due date.......there;s a
conditional format for it....which within 14 days of the due date....it would
turn "orange" and when the due date reach....it would turn "red" colour......

but now i need to add in....Let's say.....when the "orange" colour
appears (meaning within 14 days of the due date) ....it would appear on the
orange colour cell itself or another new column next to it showing .....how
many days left from the 14 days?
E.g. 14 for today, 13 for tomorrow, 12, 11, 10 ...and goes on till it
turns "red" (due date) ........and it would count again from this due
date....1 , 2, 3 days and etc .......meaning showing how many days has it due
already??.......

Pls Do help me on what formulas can i use?
Cheers to all....Thanks

 

[Biff]

Hi!

With the due date in cell A2:

=IF(A2="","",IF(A2=TODAY(),"due today",IF(TODAY()>A2,"PAST
DUE",IF(A2-14>TODAY(),"",A2-TODAY()&" day"&IF(A2-TODAY()>1,"s","")&" until
due"))))

Biff

 

[Guest]

Hi,
Do you mean by putting this formula in the "Conditional Format" in the
"date" cells??

 

[Guest]

Thank u so much Biff......i finally get what u mean....and im able to count
the days within the 14 days of due date in "orange".....

But for, after the due date itself...to count down.....it only shows out
"PAST DUE".....Is it able to show out the days how long has it been due too??

 

[Biff]

Hi!

Do you mean by putting this formula in the "Conditional Format" in the
"date" cells??

No. If the due date is in cell A2 put this formula in, say, cell B2.

I see that you also wanted a count-up of the days past due. I didn't include
that in my first formula. This one includes both a count-down from 14 days
until the due date and a count-up of days past due.

=IF(A2="","",IF(A2=TODAY(),"due today",IF(TODAY()>A2,"PAST DUE -
"&TODAY()-A2&" day"&IF(TODAY()-A2>1,"s",""),IF(A2-30>TODAY(),"",A2-TODAY()&"
day"&IF(A2-TODAY()>1,"s","")&" until due"))))

So, here's what it will display depending on which condition is met:

12 days until due
due today
PAST DUE - 5 days

What happens when the account is satisfied and is no longer past due? Or,
for that matter, if the account is satisfied before any of the above
conditions are met?

Biff

 

[Biff]

Ooops!

I'll get it straight!

Change this portion:

.....IF(A2-30>TODAY....

To:

.....IF(A2-14>TODAY....

Biff