• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate TODAY IF adjacent cell Not Blank

L

Legs60

New Member
Hi - I have conditional formatting set and the TODAY formula works fine until I enter an Actual Completion Date. At which time I have to delete the formula, and I also lose the historical data. I would like the formula to not calculate/stop calculating TODAY if the Actual Completion Date is populated. I tried =IF(L9<>"",K9,TODAY()-K9," ") and get back there are too many arguments for the function.

View attachment 34780
 
Hi ,

There is no attachment.

The IF function can take only 3 parameters at the most , which means your posted IF formula should probably be :

=IF(L9<>"",K9,TODAY()-K9)

Narayan
 
I tried that but it does not give me the final value (total number of days). I have attached a copy of the file. Just in case.

Col N Col O Col P
Target Actual Day Overdue
8/21/16 31
8/30/16 22
9/11/16 10

Using the new formula I get the same results so that works. But when I enter a date in the Actual column I get.

8/30/16 9/21/16 42612 Losing the historical data.
 

Attachments

  • Hi_2.docx
    17.3 KB · Views: 8
Hi ,

Please upload your Excel workbook , if that is possible.

Otherwise , please post all of the following :

1. The contents of any relevant cell in columns L and M

2. The formula which is in use , and the cell in which it is used

3. What the problem is with the formula.

Narayan
 
To determine the number of days upcoming or past due from the Target Date the formula used is =IF(K#,TODAY()-K#,” “) along with conditional formatting Green if <- 5 days, Yellow between – 5 and +5 days and Red if > than 5 days. The problem is when the Actual Date is populated the Past Due column continues to calculate. I have to delete the data in the Past Due column losing any historical data. I would like when the Actual Date column is populated that the Past Due column no longer calculates.

Thanks.
 

Attachments

  • SEND COPY.xlsx
    67.1 KB · Views: 5
Hi,

That works! I'll need to change my conditional formatting. Is there a way to not lose the calculation? Row 16 in column M the 3 days late is lost.

Just being greedy.

Appreciate your patience.
 
Hi ,

If you can specify what kind of formatting you want and the logic for the formatting , we can restore the CF.

Narayan
 
You must log in or register to reply here.
Back
Top