cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tomgardzinski
Level: Powered On

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

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Power Apps Staff CarlosFigueira
Power Apps Staff

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

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!

View solution in original post

Power Apps Staff CarlosFigueira
Power Apps Staff

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

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.

View solution in original post

6 REPLIES 6
Power Apps Staff CarlosFigueira
Power Apps Staff

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

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!

View solution in original post

tomgardzinski
Level: Powered On

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

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.

 

Power Apps Staff CarlosFigueira
Power Apps Staff

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

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.

View solution in original post

tomgardzinski
Level: Powered On

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

Awesome!!!

 

Thank you!

tomgardzinski
Level: Powered On

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

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)

Power Apps Staff CarlosFigueira
Power Apps Staff

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

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.

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,777)