|
Number of days left shown???
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 |
| All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com