Answered

Counting wrong number of days

(0) Share

Report

Posted on by Bosgraj

Hi,

I used the next code. It's part of larger code that must calculate how many Mondays, Tuesdays ..ect there are in a Year without the holidays and weekends.

Sum(
  With(
    {
      // generate a one-column table of all dates between start date & end date
      varDateRange: ForAll(
        Sequence(DateValue(“3-27-2023”) – DateValue(“1-1-2023”) + 1),
        DateValue(“1-1-2023”) + Value – 1
      )
    },
    CountIf(
      varDateRange,
      Weekday(Value) in [2]
    )
  )
)

But I see a weird thing happen. With this code I mis 1 Monday in the Year 2023. After some research I saw that Monday 27th is not counted as the 13th Monday of the year. If I use the 28th or March or April 4th the total number will be 13. Was do I wrong.

Can someone help me with this problem?

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

developerAJ 4,664 on at

Like (0)

Report

your format is wrong
Sequence(DateValue(“3-27-2023”) – DateValue(“1-1-2023”) + 1)

it should be
Sequence(DateValue(“27-3-2023”) – DateValue(“1-1-2023”) + 1)

Was this reply helpful? Yes No
Bosgraj 20 on at

Like (0)

Report

Thanks for your reply.

The format isn't the issue. I use US and Dutch format but both does not work.
In my app I use two datepickers instead of the dates in this example which also gave the same result in both formats.

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like (1)

Report
Hi @Bosgraj,

Your DateValue subtraction results in a decimal number that is rounded down before being served as the input to the sequence function. In short, you are missing a day in the Sequence array.

In my opinion, the best way to calculate the difference between 2 dates is by using the DateDiff() function:

Sum( With( { // generate a one-column table of all dates between start date & end date varDateRange: ForAll( Sequence(DateDiff(DateValue("1-1-2023"), DateValue("3-27-2023"),TimeUnit.Days) +1), DateValue("1-1-2023") + Value - 1 ) }, CountIf( varDateRange, Weekday(Value) in [2] ) ) )

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No