Unanswered

count of no of working days

Like (0) Share

Report

Posted on 26 Jul 2023 09:50:38 by venky232

i have used below code to count no of working days based on datepicker1 but when i select feb month showing wrong data .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 (4)

Answers (0)

venky232 on 26 Jul 2023 at 10:20:05

Like (0)

Report

Re: count of no of working days

error sequence as invalid arguments
Days is unknown or unsupported function
Rajkumar_M 3,711 Super User 2025 Season 2 on 26 Jul 2023 at 10:12:31

Like (0)

Report

Re: count of no of working days

Try this

With(
{
varStartDate: Date(Year(DataCardValue4.SelectedDate), Month(DataCardValue4.SelectedDate), 1),
varEndDate: Date(Year(DataCardValue4.SelectedDate), Month(DataCardValue4.SelectedDate) + 1, 1),
varDateRange: Filter(
AddColumns(
Sequence(Days(varEndDate - varStartDate), 0),
"Date",
DateAdd(varStartDate, Value, Days(1))
),
Not(DateAdd(varStartDate, Value, Days(1)) in Col_Holidaylist.Date1) &&
Weekday(DateAdd(varStartDate, Value, Days(1)), StartOfWeek.Sunday) in [2, 3, 4, 5, 6]
)
},
CountRows(varDateRange)
)
venky232 on 26 Jul 2023 at 10:03:17

Like (0)

Report

Re: count of no of working days

not working code sequence as some invalid arguments
Rajkumar_M 3,711 Super User 2025 Season 2 on 26 Jul 2023 at 09:59:18

Like (0)

Report
Re: count of no of working days

Hi

The issue with the formula you provided for counting the number of working days based on the selected date is that it assumes that February has 31 days. To fix this issue, you can modify the formula to use the Days function to get the number of days in the selected month.

With(
{ varDateRange:
ForAll( Sequence(Days(DataCardValue4.SelectedDate), 1),
Text(Value & "/" & Month(DataCardValue4.SelectedDate) & "/" &
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) ) ) )

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.