Formula to see if completed date is within the last seven days of the final acceptable date

Marianne Mc

Hi,

I have a due date (which is a guidance), and then a final acceptable date. I would like to see what items are completed on the last 7 days (including the last day) of the final acceptable date. If it is completed in the last 7 days of the final acceptable date it will be "yes" , otherwise blank. Any help would be greatly appreciated as I am getting nowhere.

Thank you !😊

Kelly Moore

Hey @Marianne Mc

Try this. It will need to be in its own column:

=IF(AND([Completed Date]@row >= [Final Accepted Date]@row - 7, [Completed Date]@row <= [Final Accepted Date]@row), "Yes")

Will this work for you?

Kelly

Kelly Moore

Hey @Marianne Mc

This should fix it

=IF(AND(ISDATE([Completed Date]@row), ISDATE([Final Accepted Date]@row)),IF(AND([Completed Date]@row >= [Final Accepted Date]@row - 7, [Completed Date]@row <= [Final Accepted Date]@row), "Yes"))

Does this work for you?

Kelly

Kelly Moore

Hey @Marianne Mc

Try this. It will need to be in its own column:

=IF(AND([Completed Date]@row >= [Final Accepted Date]@row - 7, [Completed Date]@row <= [Final Accepted Date]@row), "Yes")

Will this work for you?

Kelly

Marianne Mc

Hi Kelly.

Thank you so much for answering. Have you any ideas why it is coming up "Yes" for three blank rows, but the blank rows after it are blank when I convert to column formula? On these 3 blank rows my overdue column also comes up over due for some reason. I do not know if the problem is the formulas or my sheet.

Many thanks in advance. I greatly appreciate the response!

Kelly Moore

Hey @Marianne Mc

This should fix it

=IF(AND(ISDATE([Completed Date]@row), ISDATE([Final Accepted Date]@row)),IF(AND([Completed Date]@row >= [Final Accepted Date]@row - 7, [Completed Date]@row <= [Final Accepted Date]@row), "Yes"))

Does this work for you?

Kelly

Molly12345

Thank you Kelly !

Marianne Mc

Amazing Kelly, thank you so much. I was about to throw the laptop out the window. You are a life saver 😄. Have a great week