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

Marcus Papin

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

Try this...

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

Explanation:

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.")

Marcus Papin

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