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

Hi!

With the due date in cell A2:

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

Biff

"Kelly Lim" wrote in message
...
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

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

"Biff" wrote:

Hi!

With the due date in cell A2:

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

Biff

"Kelly Lim" wrote in message
...
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

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

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()-A21,"s",""),IF(A2-30TODAY(),"",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

"Kelly Lim" wrote in message
...
Hi,
Do you mean by putting this formula in the "Conditional Format" in the
"date" cells??

"Biff" wrote:

Hi!

With the due date in cell A2:

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

Biff

"Kelly Lim" wrote in message
...
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

Ooops!

I'll get it straight!

Change this portion:

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

To:

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

Biff

"Biff" wrote in message
...
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()-A21,"s",""),IF(A2-30TODAY(),"",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

"Kelly Lim" wrote in message
...
Hi,
Do you mean by putting this formula in the "Conditional Format" in the
"date" cells??

"Biff" wrote:

Hi!

With the due date in cell A2:

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

Biff

"Kelly Lim" wrote in message
...
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