Due Date Reminder with Color Warning

[ciamik]

Hi I am Faried,

Still new here and found out a Due Date formula here http://www.excelforum.com/excel-gene...-reminder.html
Unfortunately I do need to add a reminder blocked with color to each of them who comes to 30 days or entering 30 days and so on.

I can't do Conditional Formating as mentioned on the other thread as I do need them for hundreds row.
Is it possible to add another formula mentioned on http://www.excelforum.com/excel-gene...-reminder.html,

Thanks & Regards,
Faried

[canapone]

Hi,

maybe


=IF((C3-E3)<=30,C3-E3& " Days Left"," ")

or

=IF(abs(C3-E3)<=30,abs(C3-E3)& " Days Left"," ")


Hope it helps

[ciamik]

hi CANAPONE,

Thanks for your reply.
But seems is not working. It works as previous formula only adding DAYS LEFT but not the color blocking.
See the attachment.

Note: See the color block below, I want it that way (if possible)

TY&Rgds,
Faried

[Steve N.]

Why is Conditional Formatting not an option?

[ciamik]

Why is Conditional Formatting not an option?

is it possible to use Conditional Formating for multiple rows ? As I have hundreds rows to apply this reminder.

Thanks &Rgds,
Faried

[Ursul]

So...? Conditional Formatting can cover thousands of rows! Not sure I understand what the problem with CF is?

[Ursul]

Ok there were a couple of issues with your spreadsheet - wrong reference for one thing plus the IF function had to be changed to delete the text ... then I corrected the CF and now it works. Cheers

Due Date.xls

[ciamik]

Thanks Ursul,

That was perfect. So I see you manage to do that with CF.
Thanks for your assistance.

CASE CLOSED

Thanks & Regards,
Faried

[Ursul]

Hi Faried, you're welcome and thanks for the feedback. If you're happy mark the Thread as "Solved" and please click on my *Add Reputation".

Steps: 1 click in cell D3 then go to Conditional Format button and go down to the last option "Manage Rules"
2. Once here, select one of the 3 rules (hence 3 colours) and then select "Edit Rule" and you will see how the rule is done.

In this case they were all easy ones because they have pre-formatted rules - click on the 3 different rules/colours and select "Edit" to see how they were done.

Best regards

[ciamik]

you r the man,

TY&Rgds,
FD

[ciamik]

hi,

I reopen again as I found out that my formula was not working well.
The CF working fine, only the due date was not.

I got a problem with the formula when it's over than a year (see the attachment again
Also, I confused why this formula =IF(C4>E4,(C4-E4),"") did not show anything on my excel...? it comes blank
Untitled.jpg

Please, help me again master.

Thanks & regards,
faried

[canapone]

Hi,

please the file attached

RED conditional format rule

Please Login or Register  to view this content.

GREEN conditional format

Please Login or Register  to view this content.

Hope it helps

[Jim885]

Also, I confused why this formula =IF(C4>E4,(C4-E4),"") did not show anything on my excel...? it comes blank

caimik,
The formula does work, it is just designed to return a blank based on a certain condition.
The formula reads this way;
If the value of C4 is greater than the value in E4, then subtract E4 from C4, otherwise return a blank.

If you want to see something being returned instead of a blank so you know the formula is doing something then do this slight modification.
=IF(C4>E4,(C4-E4),"-")

This formula will just return a minus sign instead of a blank.

[ciamik]

Hi,

please the file attached

RED conditional format rule

Please Login or Register  to view this content.

GREEN conditional format

Please Login or Register  to view this content.

Hope it helps

Thanks.

The Conditional Formating works fine, what is not done now the formula if the date over than a year.
How can it calculate?
when I change the year into 2010, this formula =IF(C4>E4,(C4-E4),"") became false.

Ok.
I have a staff, name Jeff. He start working at January 1st, 2010. I want him to come to my desk every year to re-contract. But before that I need a warning of due date eg: 30 days.
Using =IF(C4<E4,(C4-E4),"") formula, Jeff will have -1048 by today. So its not getting close but getting farther.
I change =IF(C4>E4,(C4-E4),"") into =IF(C4<E4,(C4-E4),"") as using > will resulting blank / false.

Sorry, I do really confused with this.
Can anyone attach me a file that might help please?

Thanks

[ciamik]

caimik,
The formula does work, it is just designed to return a blank based on a certain condition.
The formula reads this way;
If the value of C4 is greater than the value in E4, then subtract E4 from C4, otherwise return a blank.

If you want to see something being returned instead of a blank so you know the formula is doing something then do this slight modification.
=IF(C4>E4,(C4-E4),"-")

This formula will just return a minus sign instead of a blank.

I see thanks man.

[ciamik]

any help for this:

I have a staff, name Jeff. He start working at January 1st, 2010. I want him to come to my desk every year to re-contract. But before that I need a warning of due date eg: 30 days.
Using =IF(C4<E4,(C4-E4),"") formula, Jeff will have -1048 by today. So its not getting close but getting farther.
I change =IF(C4>E4,(C4-E4),"") into =IF(C4<E4,(C4-E4),"") as using > will resulting blank / false.

Sorry, I do really confused with this.
Can anyone attach me a file that might help please?

Thanks

[newdoverman]

This might work for you...no time to read all the answers.