Calculating Next Monday, Wednesday, Friday

raymond.j.riosiii94806

I'm trying to write a formula to calculate the next monday, wednesday, and friday following a particular date.

The AI formula generated =IF(ISDATE([Start Date]@row), [Start Date]@row + (8 - WEEKDAY([Start Date]@row, 2)), "") using the example above; however, it generated an #INCORRECT ARGUMENT error.

The expected results should be MON, 12/30/2024, WED, 1/1/2025, and FRI, 1/3/2025.

Can someone help me figure out why?

dojones

First, make sure Week 1 - Mon is a Date column type. Then try this:

Week 1 - Mon:

=IF(ISDATE([Start Date]@row), [Start Date]@row + (9 - WEEKDAY([Start Date]@row)), "")

Week 1 - Wed:

=IF(ISDATE([Start Date]@row), [Start Date]@row + (9 - WEEKDAY([Start Date]@row)), "")

Week 1 - Fri:

=IF(ISDATE([Start Date]@row), [Start Date]@row + (13 - WEEKDAY([Start Date]@row)), "")

raymond.j.riosiii94806

Awesome, that worked. But now, let me throw in a curve ball. What if, rather than calculating the next Monday, Wednesday, and Friday, it calculated the next session date, regardless. Example, if today is 12/27, the next three return values are 12/30, 1/1, and 1/3; however, if the start date is 12/30, then the return values are 1/1, 1/3, and 1/6. Also, is it possible to account for the holidays or in other words, return only WORKDAYS equal to a Monday, Wednesday, or Friday?

Genevieve P.

Hi @raymond.j.riosiii94806

Adding 3 days on to a date is easier than the formulas above! 🙂 All you need to do is use + then the number of days:

=[Date Reference]@row + 3

or in your case

=IF(ISDATE([Start Date]@row), [Start Date]@row + 3)

For WorkDays, you can use the Workday function:

=WORKDAY([Date Reference]@row, 3)

or

=IF(ISDATE([Start Date]@row), WORKDAY([Start Date]@row, 3)

raymond.j.riosiii94806

Thank you for that Genevieve. This satisfies the first part of the problem, but I need 9 consecutive sessions auto calculated, each on their respective Monday, Wednesday, and Friday. This formula does not take into account the weekends; therefore, when I enter =IF(ISDATE([Start Date]@row,WORKDAY([Start Date]@row,3), it returns the following Tuesday, then Friday.

jmyzk_cloudsmart_jp

Hi @raymond.j.riosiii94806

Here is a demo solution.

https://app.smartsheet.com/b/publish?EQBCT=50ee7f1d90dd458f8418a99d8e01dfd8

This method dynamically calculates and displays the following nine valid session dates (Monday, Wednesday, and Friday) following a given start date while skipping any dates on holidays listed in a predefined holiday table.

https://app.smartsheet.com/b/publish?EQBCT=d1fdd38768e4438abe27d27f6c66347e

The solution uses dynamic offset values for each weekday stored in Sheet Summary Fields, such as Offset - Monday = 2, Offset - Wednesday = 4, and Offset - Friday = 6, to ensure flexibility and ease of updates. (Example: [Start Date]@row - WEEKDAY([Start Date]@row) + M#)

The formula determines the next session day by adjusting the start date using these offsets and checks against the holiday list with a COUNTIF function. The formula returns a blank value if a calculated date matches a holiday. (Example: =IF(COUNTIF({Us National Holidays 2025}, [Start Date]@row - WEEKDAY([Start Date]@row) + W# + IF([Primary Column]@row > 1, 7)) > 0, "")

It dynamically adjusts calculations based on the weekday of the start date, using WEEKDAY([Start Date]@row) to determine if the start date is already past Monday. If so, the results will be shifted to the next week by adding seven days.
(Example: (+IF(WEEKDAY([Start Date]@row) > 1, 7))

The formula adds 7 * n, where n is the number of weeks ahead, to calculate future session dates for subsequent weeks. (Example: Week 2, 3)

Week 1:

[M1] =IF(COUNTIF({Us National Holidays 2025}, [Start Date]@row - WEEKDAY([Start Date]@row) + M# + IF(WEEKDAY([Start Date]@row) > 1, 7)) > 0, "", [Start Date]@row - WEEKDAY([Start Date]@row) + M# + IF(WEEKDAY([Start Date]@row) > 1, 7))
[W1] =IF(COUNTIF({Us National Holidays 2025}, [Start Date]@row - WEEKDAY([Start Date]@row) + W# + IF(WEEKDAY([Start Date]@row) > 1, 7)) > 0, "", [Start Date]@row - WEEKDAY([Start Date]@row) + W# + IF(WEEKDAY([Start Date]@row) > 1, 7))
[F1] =IF(COUNTIF({Us National Holidays 2025}, [Start Date]@row - WEEKDAY([Start Date]@row) + F# + IF(WEEKDAY([Start Date]@row) > 1, 7)) > 0, "", [Start Date]@row - WEEKDAY([Start Date]@row) + F# + IF(WEEKDAY([Start Date]@row) > 1, 7))

Week 2:

[M2] =IF(COUNTIF({Us National Holidays 2025}, [Start Date]@row - WEEKDAY([Start Date]@row) + M# + IF(WEEKDAY([Start Date]@row) > 1, 7) + 7) > 0, "", [Start Date]@row - WEEKDAY([Start Date]@row) + M# + IF(WEEKDAY([Start Date]@row) > 1, 7) + 7)

Week 3:

[M3] =IF(COUNTIF({Us National Holidays 2025}, [Start Date]@row - WEEKDAY([Start Date]@row) + M# + IF(WEEKDAY([Start Date]@row) > 1, 7) + 14) > 0, "", [Start Date]@row - WEEKDAY([Start Date]@row) + M# + IF(WEEKDAY([Start Date]@row) > 1, 7) + 14)

Finally, the method returns only nine valid dates by counting previous results and halting calculations once the limit is reached. This approach is modular, adaptable, and efficient for managing recurring schedules with holiday constraints.

[[M4] =IF(COUNTIF([M1]@row:[F3]@row, ISDATE(@cell)) >= 9, "", IF(COUNTIF({Us National Holidays 2025}, [Start Date]@row - WEEKDAY([Start Date]@row) + M# + IF(WEEKDAY([Start Date]@row) > 1, 7) + 21) > 0, "", [Start Date]@row - WEEKDAY([Start Date]@row) + M# + IF(WEEKDAY([Start Date]@row) > 1, 7) + 21))
[W4] =IF(COUNTIF([M1]@row:[M4]@row, ISDATE(@cell)) >= 9, "", IF(COUNTIF({Us National Holidays 2025}, [Start Date]@row - WEEKDAY([Start Date]@row) + W# + IF(WEEKDAY([Start Date]@row) > 1, 7) + 21) > 0, "", [Start Date]@row - WEEKDAY([Start Date]@row) + W# + IF(WEEKDAY([Start Date]@row) > 1, 7) + 21)) ….

Finally, the following formula sums up the subsequent nine sessions.

[Next Nine Sessions] =JOIN(COLLECT([M1]@row:[F4]@row, [M1]@row:[F4]@row, ISDATE(@cell)), CHAR(10))

raymond.j.riosiii94806

@jmyzk_cloudsmart_jp - this is phenomenal, thank you! But I'll need a little help putting this together. More importantly, I'd like to see if this can be modified to simply auto-calculate the next sessionrather than skipping it and having to create sessions 10, 11, and 12 to account for the holidays that were skipped. Do you have time to connect via Zoom or another platform?

jmyzk_cloudsmart_jp

Hi @raymond.j.riosiii94806

I updated the solution to meet your requirements.

The formula shown at the bottom gets a range of dates only from [M1] to [F4] with the COLLECT function, "COLLECT([M1]@row:[F4]@row,[M1]@row:[F4]@row, ISDATE(@cell))", and use the INDEX function to get the Session 1 to 9.

This method prevents the formula from getting too complicated because there are alternate-day holidays, such as Thanksgiving Day(a national holiday) and Black Friday (a state holiday).
Otherwise, you must find the following Monday, Wednesday, or Friday and check if it is a holiday. If it is, check the next three days of the week, and if so, check the next.

If you want to connect with me via Zoom or another platform, please contact me by email on my profile page.

https://app.smartsheet.com/b/publish?EQBCT=6b5841bab41f4bd68dada5fdab0e9564

Formulas

• [S1] =INDEX(COLLECT([M1]@row:[F4]@row [M1]@row:[F4]@row ISDATE(@cell)) 1)
• [S2] =INDEX(COLLECT([M1]@row:[F4]@row [M1]@row:[F4]@row ISDATE(@cell)) 2)
• [S3] =INDEX(COLLECT([M1]@row:[F4]@row [M1]@row:[F4]@row ISDATE(@cell)) 3)
• ….
• [S9] =INDEX(COLLECT([M1]@row:[F4]@row [M1]@row:[F4]@row ISDATE(@cell)) 9)

raymond.j.riosiii94806

@jmyzk_cloudsmart_jp, I attempted to connect with you via email; however, you have not responded as of yet. Can we try to coordinate a time to meet and discuss your proposed solution above? Thank you so much!