cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syhrh
Dual Super User
Dual Super User

Resume working date formula

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.

 

anonymous21_0-1655370754241.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

17 REPLIES 17
theapurva
Super User
Super User

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?

-----------------------------------------------------------------------------------
Liked My Solution? Click on the Thumbs Up below.
Did I answer your Question? Please Click Accept as Solution below

Want to Support me? Buy Me A Coffee

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
Super User
Super User

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

 

theapurva_0-1655451356075.png

 

-----------------------------------------------------------------------------------
Liked My Solution? Click on the Thumbs Up below.
Did I answer your Question? Please Click Accept as Solution below

Want to Support me? Buy Me A Coffee

@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. 

anonymous21_0-1655451716660.png

 

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,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

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

anonymous21_0-1655798387037.pngit'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. 

 

 

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,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

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

anonymous21_0-1655801089998.png

The data type there is record when it expects DateTime.

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,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (5,113)