web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Apps count working days

Power Apps

Answered

count working days

(0) Share

Report

Report

Posted on by venky232

i have one datepicker ,i want to count no of working days, below code is working but showing data for FEB,APRIL,NOV month. how to fix this?

With(
{
varDateRange: ForAll(
Sequence(31,1),
Text(Month(DataCardValue4.SelectedDate) & "/" & Value & "/" & Year(DataCardValue4.SelectedDate),"DD/MM/YYYY")
)
},
// show only dates Monday to Friday and exclude holidays
CountIf(
varDateRange,
And(
Weekday(Value,StartOfWeek.Sunday) in [2, 3, 4, 5, 6],

Not(Value in Col_Holidaylist.Date1)
)
)
)

Categories:

Building Power Apps

I have the same question (0)

All responses Img

All responses (2)

Answers Img

Answers (1)

Sort by

Sundeep_Malik 6,501 on at

Like (0)

Report
Copy link

Link copied!
Hey @venky232

Actually the expression/formula/function you are using has the days set to constant 1-31, but number of days vary from month to month

To fix this, you can use a more dynamic approach that generates a sequence of dates for the entire month and then filters out weekends (Saturday and Sunday) and holidays. Here's how you can achieve this:

With(
{
varStartDate: StartOfMonth(DataCardValue4.SelectedDate),
varEndDate: EndOfMonth(DataCardValue4.SelectedDate)
},
CountIf(
DateAdd(varStartDate, Value, Days),
Weekday(DateAdd(varStartDate, Value, Days), StartOfWeek.Sunday) in [2, 3, 4, 5, 6] // Monday to Friday
&& Not(DateAdd(varStartDate, Value, Days) in Col_Holidaylist.Date1) // Exclude holidays
)
)

varStartDate stores the first day of the selected month and varEndDate stores the last day of the selected month.

CountIf function to count the number of dates in the range between varStartDate and varEndDate that meet the specified conditions.

DateAdd function generates a sequence of dates from varStartDate to varEndDate based on the sequence number (Value) passed to it.

Weekday function is used to check if the date is a weekday (Monday to Friday) and filter out weekends.

Not function excludes dates that are present in the Col_HolidayList.Date1 column, which presumably contains the list of holidays.

I hope this helps 🙂

Was this reply helpful? Yes No
Verified answer

v-mingkz-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @venky232,
Please try this formula:
CountIf( Filter( With({targetMonth: Month(DataCardValue4.SelectedDate), targetYear: Year(DataCardValue4.SelectedDate)}, With({startDate:Date(targetYear, targetMonth, 1), endDate:DateAdd(DateAdd(Date(targetYear, targetMonth, 1),1,TimeUnit.Months),-1,TimeUnit.Days) }, ForAll( Sequence(DateDiff(startDate,endDate,TimeUnit.Days)+1), Date(targetYear, targetMonth, Value) ) ) ), Weekday(Value) > 1 And Weekday(Value) < 7 ), Not(Value in Col_Holidaylist.Date1) )

Best Regards,
Kyrie

Was this reply helpful? Yes No

Join the conversation