Calculate the working days by subtracting weekends and holidays?

(0) Share

Report

Posted on by RobHajj98

496

Hello,

I hope all is well,

Problem:

I want to calculate the working days by getting the number of total days between the selected two dates from two date pickers and subtracting weekends and holidays.

My logic worked fine, I succeeded to calculate the working days but whenever I have a weekend day which is a holiday at the same time, my logic is subtracting 2 days from the total selected days, which is not logical because only it's only one day that should be subtracted.

What I used:

// The first part of the equation calculates the total number of days selected between the range of date

1 + ((DateDiff(
 DatePicker1.SelectedDate,
 DatePicker2.SelectedDate,
 Days
)) * 5 - ((Weekday(DatePicker1.SelectedDate) - Weekday(DatePicker2.SelectedDate))*2)) / 7


// The second part checks if the weekday is a Sunday(1) or Saturday(7) and removes one day (-1) from the total else it removes nothing ( -0) 

 - Switch(
 Weekday(DatePicker2.SelectedDate),
 7,
 1,
 0
) - Switch(
 Weekday(DatePicker1.SelectedDate),
 1,
 1,
 0
) 

// This part counts the number of days that are stored in Dataverse as holidays and exists in the selected range of date and subtract them from the total 

- CountIf(
 colholiday,
 'Holiday Date' >= DatePicker1.SelectedDate,
 'Holiday Date' <= DatePicker2.SelectedDate
)

What I want:

I want to check if the selected range of dates contains a day that is a weekend and a holiday at the same time, only 1 day should be removed from the total.

In my logic above, I'm checking if it's a weekend day and if yes, I'm removing 1 day but whenever it's a weekend day and a holiday, it's removing 2 days.

So what is the best practice to achieve the desired result above?

Thank you 🙂.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

v-xiaochen-msft on at

Like (1)

Report
Hi @Roberto_el_Hajj ,

Please try this:

1 + ((DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Days )) * 5 - ((Weekday(DatePicker1.SelectedDate) - Weekday(DatePicker2.SelectedDate))*2)) / 7 // The second part checks if the weekday is a Sunday(1) or Saturday(7) and removes one day (-1) from the total else it removes nothing ( -0) - Switch( Weekday(DatePicker2.SelectedDate), 7, 1, 0 ) - Switch( Weekday(DatePicker1.SelectedDate), 1, 1, 0 ) // This part counts the number of days that are stored in Dataverse as holidays and exists in the selected range of date and subtract them from the total - CountIf( colholiday, 'Holiday Date' >= DatePicker1.SelectedDate, 'Holiday Date' <= DatePicker2.SelectedDate )+ Countrows( Filter( AddColumns( Filter(colholiday, 'Holiday Date' >= DatePicker1.SelectedDate, 'Holiday Date' <= DatePicker2.SelectedDate ), "Index",Weekday('Holiday Date') ),Index=1||Index=7))

Best Regards,

Wearsky

Was this reply helpful? Yes No
RobHajj98 496 on at

Like (0)

Report

Hello @v-xiaochen-msft, thank you so much, it's exactly what I needed, I really appreciate it.

Was this reply helpful? Yes No