Auto selecting a Date that doesn't occur on the weekend

[Anonymous]

Hello Community,

I have encountered a roadblock. I've already consulted this blog, which helps if you want to know the number of workdays between a beginning date, and an end date.

I want to do something a little different.

I have a process called Follow-Ups, where I want to follow up with a customer 3 months (90 days works) after their 'purchase'. I tried using the formula in the blog link provided above which provides you with a number, then tried using that number to add in the days.

My current defaultdate formula is:

DateAdd(DataCardValueExitDate.SelectedDate, 90, Days)

Again, I want to make sure the 90th day is not a Saturday or Sunday (I'll factor in holidays after I build the holiday table). And it would be nice to always round up, so if it lands on a Saturday, round to the Monday (plus 2 more days).

Any thoughts?

[CarlosFigueira · Accepted solution]

You can use the following expression to get 90 days if on weekday or the following Monday:

If(
    Weekday(DataCardValueExitDate.SelectedDate) > 2,
    DateAdd(DataCardValueExitDate.SelectedDate, 90, Days),
    DateAdd(DataCardValueExitDate.SelectedDate, 90 + 3 - Weekday(DataCardValueExitDate.SelectedDate), Days))

The idea is that if the selected date is Sunday (Weekday = 1) or Monday (Weekday = 2) then 90 days from that date will fall on a weekend (Saturday and Sunday, respectively). So if the selected date is not one of those days, simply add 90; otherwise add the 90 days, plus 2 for Saturday and 1 for Sunday.

Hope this helps!

[Anonymous]

How can this be a read across to 180 days and 365 days?

I also have a 6 month (180 days), and 12 month (365 days). For some reason the code you provided isn't a straight copy paste and change 90 days to 180 days.

[CarlosFigueira · Accepted solution]

Good point - instead of trying to calculate the final weekday based on the original date, you can check the week day of the final day, and adjust your calculations based on it.

If(
    Weekday(DateAdd(DataCardValueExitDate.SelectedDate, 90, Days), StartOfWeek.Saturday) > 2,
    DateAdd(DataCardValueExitDate.SelectedDate, 90, Days),
    DateAdd(
        DataCardValueExitDate.SelectedDate,
        90 + 3 - Weekday(DateAdd(DataCardValueExitDate.SelectedDate, 90, Days), StartOfWeek.Saturday),
        Days))

In the expression above, you can change all instances of '90' to '180' or '365' and you should have the logic you want.

[Anonymous]

Awesome!!!

Thank you!

[Anonymous]

You've been a great help so far! I have one more final request for the date. I also don't want the date selected to be a stat holiday, like December 25 (Christmas Day). Sending an auto email on that day would likely not be well received by the customer. I have been trying to work with a table or SP list of holidays but couldn't figure out to incorporate it into the logic. Here is what I have tried, but it is not working. Any thoughts?

If( Weekday(DateAdd(DataCardValueExitDate.SelectedDate, 90, Days), StartOfWeek.Saturday) > 2, DateAdd(DataCardValueExitDate.SelectedDate, 90, Days), DateAdd( DataCardValueExitDate.SelectedDate, 90 + 3 - Weekday(DateAdd(DataCardValueExitDate.SelectedDate, 90, Days), StartOfWeek.Saturday), Days)) + CountIf(GCHolidays, Date >= DataCardValueExitDate.SelectedDate, Date <= DataCardValue3MonthDate.SelectedDate)

[CarlosFigueira]

An alternate implementation of the "skip weekends" logic (and IMO easier to read) is the following:

First(
    Filter(
    AddColumns(
        [0,1,2],
        "FutureDate",
        DateAdd(DataCardValueExitDate.SelectedDate, 90 + Value, Days)),
    Weekday(FutureDate, StartOfWeek.Saturday) > 2)).FutureDate

Which creates a temporary table with the 3 days starting from 90 days past the selected date, filters it for weekdays only, and takes the first one of those. With this, it's easier to add the logic to also skip holidays:

First(
    Filter(
    AddColumns(
        [0,1,2,3,4,5,6,7,8,9,10],
        "FutureDate",
        DateAdd(DataCardValueExitDate.SelectedDate, 90 + Value, Days)),
    Weekday(FutureDate, StartOfWeek.Saturday) > 2,
    IsBlank(LookUp(Holidays, Date = FutureDate)))).FutureDate

Where Holidays is a table/collection that has a list of the holidays that you want to skip.