Resume working date formula

[syhrh]

Hi, 

I'm trying to calculate a column, Resume Working Date. The user will choose the StartDate and EndDate then the Resume Working Date is automatically calculated but the date excludes public holidays and weekends.

I tried to AddDate to the next day of the EndDate and combine the formula of trying to exclude public holidays and weekends but it didn't work and has error. Can anyone please help me to fix it to work? Or I cannot combine those formulas so that's why it has error? If yes, then how to fix it?

Thank you.

[theapurva]

there is no issue in your formula,
I am able to use it correctly.
DateAdd(
DatePicker1.SelectedDate,
Value(TextInput2.Text),
Days
)

Can you please tell us the error its showing?

[syhrh]

Hi @theapurva, the DateAdd has no error but I'm trying to combine both DateAdd and the 'With' function because I want the date added to exclude weekends and public holidays. I tried to combine them using a semicolon but has an error message: Expected operator. We expect an operator such as +,*, or & at this point in the formula.

The 'With' function formula returns number and the DateAdd returns date value so it didn't work when I combine both formulas. Do you know another formula or a way to combine both formulas to exclude the weekends and public holidays? 

[theapurva]

Instead of CountIf in your formula, you have to use RemoveIf if the date falls on holiday or weekend.

You can paste your formula here so i can edit and give you

[syhrh]

@theapurva 

DateAdd(DataCardValue17.SelectedDate, 1, Days) ;
With(
    {
        // generate a one-column table of all dates between start date & end date
        varDateRange: ForAll(
            Sequence(DataCardValue17.SelectedDate - DataCardValue2.SelectedDate + 1),
            DataCardValue2.SelectedDate + Value - 1
        )
    },
    If(
        And(
            IsBlank(DataCardValue2.SelectedDate),
            IsBlank(DataCardValue17.SelectedDate)
        ),
        // show nothing if any date pickers are blank
        0,
        // show only dates Monday to Friday and exclude holidays
        RemoveIf(
            varDateRange,
            Or(
                Weekday(Value) in [1,7],
                (Value in Holidays.StartDate)
            )
        )
    )
)

is it like this? I have some errors. 

[v-jefferni]

Hi @syhrh ,

Could you please share more details about your scenario? Where would you like to display the results, on a Label/TextInput box? In the If statement I see 0 is the output when BOTH Date Pickers are blank, so the final output should be a number, which made sense because you were using CountIf function. 

So, what's the purpose of the first line of formula?

DateAdd(DataCardValue17.SelectedDate, 1, Days) ;

You cannot output two kinds of data in a single Label. With your original formula, there would be a date and a number, it's not correct. If you would like to exclude weekends and holidays from a Data Picker control, it's not possible. You can only display an error message using the Notify function OnChange of the Date Picker to tell users that they have selected an incorrect date. 

Best regards,

[syhrh]

@v-jefferni I would like to display the result in something like this

it's a date and time column and view mode only. It is automatically calculated when the user inputs the StartDate and EndDate

The purpose of that formula is that I want the Resume Work Date to start one day after the EndDate. I'm trying to make the Resume Work Date to skip the public holidays and weekends and only display the working weekdays. The formulas are wrong and cannot be combined but I don't know how to fix the issue. 

[v-jefferni]

Hi @syhrh ,

So it's a Date Picker in view mode, right?

If so, please try below formula in the DefaultDate property:

With(
    {
        // generate a one-column table of all dates that 7 days after end date
        varDateRange: ForAll(
            Sequence(7),
            DateAdd(DataCardValue17.SelectedDate, Value, Days)
        )
    },
    If(
        Or(
            IsBlank(DataCardValue2.SelectedDate),
            IsBlank(DataCardValue17.SelectedDate)
        ),
        // show nothing if any date pickers are blank
        Blank(),
        // show only dates Monday to Friday and exclude holidays
        LookUp(
            varDateRange,
            Not(
                Or(
                    Weekday(Value) in [1,7],
                    (Value in Holidays.StartDate)
                )
            )
        )
    )
)

Best regards,

[syhrh]

@v-jefferni Yes, it is a date picker with view mode only. I tried your formula but it has error.

The data type there is record when it expects DateTime.

[v-jefferni · Accepted solution]

Hi @syhrh ,

Add .Value after the LookUp function to get the date value:

With(
    {
        // generate a one-column table of all dates that 7 days after end date
        varDateRange: ForAll(
            Sequence(7),
            DateAdd(DataCardValue17.SelectedDate, Value, Days)
        )
    },
    If(
        Or(
            IsBlank(DataCardValue2.SelectedDate),
            IsBlank(DataCardValue17.SelectedDate)
        ),
        // show nothing if any date pickers are blank
        Blank(),
        // show only dates Monday to Friday and exclude holidays
        LookUp(
            varDateRange,
            Not(
                Or(
                    Weekday(Value) in [1,7],
                    (Value in Holidays.StartDate)
                )
            )
        ).Value
    )
)

Best regards,