Forum Discussion

Brass Contributor

Jun 20, 2023

Solved

If today's date is greater than a certain date by a number of days

Hi All Good morning I want an equation that shows a note if today's date is four days greater than the date in field A6 A6=16/06/2023 Today date = 20/06/2023

excel

Formulas and Functions

HansVogelaar
Jun 20, 2023
Saud

In another cell:

=IF(A6=TODAY()-4, "The date in A6 is four days ago!", "")

or perhaps

=IF(AND(A6<>"", A6<=TODAY()-4), "The date in A6 is four or more days ago", "")

HansVogelaar

MVP

Jun 21, 2023

Saud

Let's look at column D.

You check for D2<=TODAY()-4, i.e. D2 is 4 or more days before today.

You also check for D2=TODAY(), i.e. D2 is today's date.

If neither is satisfied, the formula continues to the last bit that returns either "task is not due" or "in progress". This will happen if D2 is in the future, but also if D2 is 1, 2 or 3 days before today.

What do you want to happen if D2 is 1, 2 or 3 days before today?

Saud

Brass Contributor

Jun 21, 2023

A beautiful and important question. On the first to the third day, if he did not complete any of the required tasks, I want the note “You didn't complete the tasks” to appear. As soon as the fourth day arrives, a note is supposed to appear that he is “Delay in monitoring tasks.”

HansVogelaar
MVP
Jun 21, 2023
Saud

Try this:

=IF(COUNTIF(D3:D19,"=")=0,"You completed the tasks",IF(AND(D2<>"",D2<=TODAY()-4),"Delay in monitoring tasks",IF(AND(D2<>"",D2<=TODAY()-1),"You didn't complete the tasks",IF(D2=TODAY(),IF(COUNTIF(D3:D6,"<>")+COUNTIF(D8,"<>")+COUNTIF(D10,"<>")+COUNTIF(D12,"<>")+COUNTIF(D14,"<>")+COUNTIF(D16:D19,"<>")=0,"You did not complete the tasks","in progress"),IF(COUNTIF(D3:D6,"<>")+COUNTIF(D8,"<>")+COUNTIF(D10,"<>")+COUNTIF(D12,"<>")+COUNTIF(D14,"<>")+COUNTIF(D16:D19,"<>")=0,"task is not due","in progress")))))
- Saud
  Brass Contributor
  Jun 21, 2023
  HansVogelaar
  
  The function did not work like this. After many attempts, I was able to reach the required result with the following equation:
  
  =IF(COUNTIF(D3:D19;"=")=0;"You completed the tasks";IF(D2<=TODAY()-4;"Delay in monitoring tasks";IF(AND(D2<=TODAY()-1);IF(COUNTIF(D3:D6;"<>")+COUNTIF(D8;"<>")+COUNTIF(D10;"<>")+COUNTIF(D12;"<>")+COUNTIF(D14;"<>")+COUNTIF(D16:D19;"<>")=0;"You did not complete the tasks";"in progress");IF(AND(D2>=TODAY()-1);IF(COUNTIF(D3:D6;"<>")+COUNTIF(D8;"<>")+COUNTIF(D10;"<>")+COUNTIF(D12;"<>")+COUNTIF(D14;"<>")+COUNTIF(D16:D19;"<>")=0;"task is not due";"in progress")))))
  
  Thank you very much for your time that you gave me, you inspired me to reach the result, Thank you again