Displaying status of a deadline

Posted by Frank on August 15, 2001 2:28 PM

I have a "Due Date" column and to the right a column in which I would like to have the message "Due Today" on the actual due date, "Past Due" after the the due date and blank prior to the due date. Right now I have the formula: =IF(TODAY()&LT;$D1233,"","Due Now!") However, this doesn not address the need for "Past Due!" after the due date. How can I add in to the string =IF(TODAY()>$D1233,"Past Due!") I have little experience with string statements.

Thanks,

Frank

Posted by GREGC on August 15, 2001 2:40 PM

=IF(TODAY()&LT;$D1233,"",IF(TODAY()=D1233,"Due Now!","PAST DUE")

Posted by IML on August 15, 2001 3:39 PM

Since you said you were new to strings, you could add a little to GREGC's formula, for example:

=IF(TODAY()&LT;$D1233,"",IF(TODAY()=D1233,"Due Now!",TODAY()-D1233&" DAYS PAST DUE"))

may give you a little better idea where your past due items stand. Similary, you could count down days until due if interested.

Good luck.

Posted by Frank on August 16, 2001 7:20 AM

Thanks IML and GREGC...

Wow, I was close. Had a few misplaced commas and parenthesis.

thanks

Posted by Frank on August 16, 2001 7:30 AM

Yes, I would like to see how it could count down days...

That would be helpful as well.

thanks again.

Posted by IML on August 16, 2001 7:37 AM

Re: Yes, I would like to see how it could count down days...

Here you go.

=IF(TODAY()&LT;$D1233,"Due in "&D1233-TODAY()&" day(s)",IF(TODAY()=D1233,"Due Now!",TODAY()-D1233&" DAY(S) PAST DUE"))

good luck. That would be helpful as well. thanks again. : Since you said you were new to strings, you could add a little to GREGC's formula, for example

Posted by Frank on August 16, 2001 9:22 AM

Boy I'm sorry I started this...

The people using this now are asking "Hey, maybe you can make the "Past Due" flash in red. And the "Due Now" in yellow!". Well we'll see how it goes. I think I'll probably be back later for help with a Visual Basic version of this. Why did I have to open my mouth in the first place :^)

Thanks again,

Frank

Posted by IML on August 16, 2001 9:59 AM

VBAers may be able to provide a flashy answer...

but you can use conditional formatting to change the color or highlight.
simply activate the cell you have the formula in, and goto Format - Conditional Formatting
Under condtion 1, change the drop down box to "formula is" and put the formula
=TODAY()=$D$1233
Now select the format box and select, for example, a yellow highlight.
Hit the add button and repeat the same steps with the formula
=TODAY()>$D$1233
and select, for example, a red background.
If you note the absoluting of cell D1233, you can simply activate the cell again, hit the formatting icon (paint brush) and highlight all the additional cells you would like to acheive this effect with.