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:

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“

    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
    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
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“

    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
    Molly12345 ✭✭
    edited 02/20/23

    Thank you Kelly !

  • Marianne Mc
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!