Share via

Stop due date counting when completion date is entered in a cell

Anonymous
2020-01-07T15:30:23+00:00

Hello

I have a tracker in which assessments are tracked. Each assessment has its own tab.

A10 is =Today()

H9 = The start date

M9 = The number of days assigned for that assessment

R9 = Completion Date

R1 is 'Due Date' =WORKDAY(H9,M9)

R1 has conditional formatting to show green if todays date is less than R1, and red if todays date is greater than R1 - red meaning overdue and orange meaning on track.

A10 is obviously going to continually count on, meaning even if it is completed on time, it will eventually show overdue.

I would like to be able to enter a completion date into R9, which would turn the cell green, and not be overruled by overdue when =today() counts past the due date... does that make sense?

Many thanks

Luke

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2020-01-07T16:00:54+00:00

Select R1.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=NOT(ISBLANK(R9))

Click Format...

Specify green as fill color.

Click OK, then click OK again.

Was this answer helpful?

1 person found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-01-19T02:49:49+00:00

    Hi Luke,

    Glad to hear that you’ve get the knowledge to implement the idea about the requirement you’re looking for. I would like to add the summary for benefiting the other members in this community who’re looking for similar requirement:

    Symptom:

    Stop counting the due date when completion date is entered.

    Environment:

    Excel, Windows 10

    Suggestion:

    Use Conditional Formatting>select New Rule>Select 'Use a formula to determine which cells to format'.

    Type the following formula:

    =NOT(ISBLANK(R9))

    Click Format...

    Specify green as fill color.

    I also test this on our side, when I type Completion Date on R9, R1 cell will change to green as below:

    Regards,

    Qing

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-01-08T04:03:43+00:00

    Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)

    There are at least 2 other conditions that you might want to keep track of.

    http://www.mediafire.com/file/arat9j7z3p49ajq/01_07_20.xlsx/file

    http://www.mediafire.com/file/7py6hicw5vwqzgp/01_07_20.pdf/file

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-01-07T16:34:37+00:00

    Thank you

    Was this answer helpful?

    0 comments