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

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 !😊

Tags:

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• 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!

• ✭✭✭✭✭✭

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

• edited 02/20/23

Thank you Kelly !

• 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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!