Formula to calculate the Thursday after a specific date

Hi

I have a cell containing the date at which some engineering tasks are completed (Complete date). I need to display in a separate cell the release date which is the new Thursday following the Complete date (or the Complete date if the Complete date is a Thursday)

In a previous thread I found the following formula which works to calculate the next Monday

=IF(WEEKDAY(Date@row) = 1, Date@row - (WEEKDAY(Date@row) - 5), Date@row - (WEEKDAY(Date@row) - 5) + 7)

but I cannot figure out how this formula works and how to modify it to make it work for Thursday.

Thank you for your help in advance

Xavier..

Tags:

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @xav 

    Hope you are fine, please try the following formula:

    =IF(WEEKDAY(Date@row) = 5, Date@row, Date@row - (WEEKDAY(Date@row) - 5) + 7)

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @xav 

    Hope you are fine, please try the following formula:

    =IF(WEEKDAY(Date@row) = 5, Date@row, Date@row - (WEEKDAY(Date@row) - 5) + 7)

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • X M
    X M ✭✭✭✭
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @xav

    You are welcome

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • X M
    X M ✭✭✭✭

    @Bassam.M Khalil

    Hi Bassam, it seems that my requirements were not clear enough for that formula.

    When I enter a date between Monday and Wednesday I need the formula to retrieve the upcoming Thursday of the same week. When I enter a date after after the Thursday of the week , it should retrieve the Thursday of the following week.

    Here are the user cases:

    • if 03/14, 03/15, 03/16 or 3/17 is the date entered, the formula should retrieve 03/18
    • if 03/18 is the date entered the formula should retrieve 03/18
    • if 03/19 is the date entered the formula should retrieve 03/25

    Does that make sense?

    Thanks

    Xavier..

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!