Unanswered

count of no of working days

(0) Share

Report

Posted on 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:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
venky232 on at

Like (0)

Report
Copy link

Link copied!

not working code sequence as some invalid arguments

Was this reply helpful? Yes No
Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!

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)
)

Was this reply helpful? Yes No
venky232 on at

Like (0)

Report
Copy link

Link copied!

error sequence as invalid arguments
Days is unknown or unsupported function

Was this reply helpful? Yes No