Creating cells that draw attention .

Topic

New Reply

In the attached worksheet, I want to keep track of guests’ property that leaves it in the facility after they had checked out. We will send out three (3) notice letters every thirty (30) days from departure date. I want to show the incremental 30 day dates in columns L, N, P. I want these dates to be blank if the columns J and K are completed. If Columns J and K are not completed ( filled out) and the current date is past any one of the NOTICE DATES, and the Notice Mailed Date ( Columns M, O and Q) are not completed, then the 30, 60, 90 Day Notice Dates cells (Columns L, N and P) are conditionally formatted and filled with RED.

Is this too much for a cell to do?

Thanks

Reply | Quote

Replies

MNN,

This can be accomplished via formula and conditional formatting. That said, however, if you want to keep the history, e.g. first 30 day notice mailed and then the property is subsequently picked up do you want the 30 day notice date to still show or be blanked? The process then becomes one for macros if you want to preserve the history. Of course, Steve or Zeddy may prove me wrong on this! 😆 :cheers:

Something like this?
36590-MNN

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Yes if the property is picked up after the 90 day letter, then I would want the history to remain that the notification letters went out. If the property was picked up before the 30 day notification went out then the 30, 60 & 90 day notice dates would be erased.

The formula is getting complicated. Can anyone assist with this

Reply | Quote

MNN,

I think these formulas along with conditional formatting will do the trick.
[noparse]
L7: =IF(AND($G7″”,$F7″”,OR($K7=””,$K7>$F7+30)),$F7+30,””)
N7: =IF(AND($G7″”,$F7″”,OR($K7=””,$K7>$F7+60)),$F7+60,””)
P7: =IF(AND($G7″”,$F7″”,OR($K7=””,$K7>$F7+90)),$F7+90,””)
[/noparse]
Once entered in the indicated cells they can be filled down.
Note: you’ll get the little triangles in the upper left corner of the cells indicating that the formula refers to empty cells, you can tell Excel to ignore this error.
36595-MNN
Conditional formatting looks like this:
36596-MNNConditional
HTH :cheers:

Test File: 36597-MNN-Customer-Property-Control-log

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks but what I want to see is columns M, O, Q become red if there is a date in columns L, N, P (30,60,90 Day Notice date columns).
If there is no date in these 30,60,90 Day Notice date columns, then the “Notice Mailed” columns do not need any Highlighting.

Thanks in advance for your assistance.

Reply | Quote

MNN,

You just need to adjust the columns you apply the conditional formatting formula to by 1 column. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for the insight. Could you give me hand understanding how the Conditional Formatting formula works =AND(NOW()>L7,L7″

Thanks again.

Reply | Quote

Conditional formatting works to format a cell a specified way when the condition setup is TRUE. the formula:
=AND(NOW()>L7,L7″”)

Will be TRUE under 2 criteria: when the current date and time [=NOW()] is greater than the value of L7 AND when L7 is not blank (or a null string).

Steve

Reply | Quote