Need help getting a Date which is linked by a predecessor to default to the nearest Thursday

Hello all,

I'm looking to get a date to populate by pulling a predecessor, but default to the nearest Thursday from that date. I have tried several formulas but am not able to get this to work.

To break it down more, I'm looking for the First Guest Check-in Date to be the Thursday after the Contingency End Date see image below.


  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    Try this...

    =IF(WEEKDAY([End Date]@row) >= 5, 7-(WEEKDAY([End Date]@row) - 5), (5 - WEEKDAY([End Date]@row))) + [End Date]@row


    WEEKDAY( ) returns the number that represents the day of the week:

    If the end date falls on a Thursday (5), you want to add 7 days to the end date. On a Friday, add 6 days. On a Saturday, add 5 days. 7-(WEEKDAY([End Date]@row) - 5) + [End Date]&row

    End date falling on Thursday through Saturday can be represented as WEEKDAY([End Date]@row) >= 5.

    If the end date falls on Sunday through Wednesday, (5 - [End Date]@row), you want to add the number of days between Thursday and that day to the end date. (5 - [End Date]@row) + [End Date]@ row

    Combine these into an IF() function.

    If "#INVALID DATA TYPE" bothers you, put the formula into an IFERROR( ) function.

    IFERROR( ____ , "Oops! Missing End Date.")

  • If I try to put this formula in the cell it takes away the predecessor. How do I like it so that The Date that shows in Column Start Date Row First Guest Check-in is the Thursday after Column End Date Row Contingency?

    It doesn't seem to want to let me use the formula

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!