Power Platform Community Forum Thread Details

Hi All,

In Dataverse, I have a working formula column that calculates a "Target_Date" by adding 24 hours if a text column value is "Rush" and 48 hours if the value is "Normal", comparing it to the "Created_on_date". This formula works fine for basic time addition.
However, the requirement is to calculate these target dates using only working days. This means I need to exclude non-working days, specifically "Saturday & Sunday". Additionally, I have a custom table named "Holiday_List", and the dates listed in this table must also be excluded from the calculation.
How can I modify this logic to correctly calculate the "Target_Date" while accounting for weekends and the holidays listed in my custom table?

formula currently used -

IfError(If(Value(Priority_Manual)=573410002, DateAdd(Created_on_date,1,TimeUnit.Days),DateAdd(Created_on_date,2,TimeUnit.Days)),Now()+1)
 
 

Categories:

Using Apps Using Formulas

General Questions

Hi there,

It is complex formula, since you have to create a collection with dates between Now and your chosen date and then increment 'workingdays' if date is a workday + not an holiday.

It should look like that:

With(
    {
        _holidays: [DateValue("08/12/2025")] //Here you have all holidays as a list
    };
    //You count every day which is not sunday or saturday, and not in your holidays list. Then, you count how many days.
    CountRows(
        Filter(
            ForAll(
                Sequence(
                    DateDiff(
                        Today();
                        DateValue("12/25/2025"; "en-us")
                    )
                );
                If(
                    Weekday(Today()+ Value) in [2;3;4;5;6] && !((Today()+ Value) in _holidays);
                    true;
                    false
                )
            );
            Value
        )
    )
)

I hope it helps!
_________________________________________________

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.