Answered

count working days

(0) Share

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:

General Questions

All responses (2)

Answers (1)

Verified answer

v-mingkz-msft Microsoft Employee on at

Like (1)

Report
Re: count working days

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
Sundeep_Malik 6,480 on at

Like (0)

Report
Re: count working days

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 🙂