Calculate working days

[Radoslavov]

Hi all, i know that there are many post and blogs discussions on this topic, but unfortunately neither one of them was able to resolve my issues so I'm posting it here.

Here is the story:

I have a vacation request app, user will choose start and end date of the vacation and the app will show him how many days is this. 

So far its working.

Some employees however are in administration, and they don't work Saturday and Sunday, so i need to exclude weekends from the total days. The employee will select a checkbox that says "Exclude weekends", and here everything goes wrong, because the calculation shows 2 working days, where clearly they are 5 (4th, 5th, 6th, 7th, and 8th of December)

Here is my formula:

If(chbNoWeekDays.Value=false,DateDiff(dateFrom.SelectedDate,DateTo.SelectedDate,TimeUnit.Days)+1, RoundDown(DateDiff(dateFrom.SelectedDate, DateTo.SelectedDate,TimeUnit.Days) / 7, 0) * 5 +
Mod(5 + Weekday(DateTo.SelectedDate,StartOfWeek.Monday) - Weekday(dateFrom.SelectedDate,StartOfWeek.Monday), 5)+1)

Now, if you are asking why i have this +1 day, this is because without it, the system will count everything one day less so instead of 7 days the user will see 6, instead of 5 working days he will see 4, weird, but i could find other solution on that.  

This is without the +1

So this was just in case you are wondering.

Any ideas what is wrong with my calculation formula, that when i take out the weekdays, the system show wrong numbers?

Regards,

Kiril

[timl · Accepted solution]

Hi @Radoslavov 

I'd try the following to see if that works better -

If(chbNoWeekDays.Value=false,
   DateDiff(dateFrom.SelectedDate,DateTo.SelectedDate,TimeUnit.Days)+1, 
   With({ startDate:dateFrom,
       endDate:DateTo
      },
      RoundDown(DateDiff(startDate, endDate, TimeUnit.Days) / 7, 0) * 5 +
      Mod(5 + Weekday(endDate) - Weekday(startDate), 5) + 1
  )
)

[Chris-D]

Hi @Radoslavov, 

Here's your solution. I was working on it earlier but got busy. It creates a list of days and then counts how many are Monday to Friday 🙂

// Use With() to set up some input parameters
With({
    StartDate: DatePicker1_2.SelectedDate,
    EndDate: DatePicker1_3.SelectedDate,
    IncludeLastDay: true
},
    // Create a list of all weekdays
    With({
        // Use ForAll() to loop though each day in range
        WeekDays: ForAll( 
            // Sequence() creates a number list
            // DateDiff() returns the number of days between 2 dates
            // +1 if we are to include the last day
            Sequence( DateDiff( StartDate, EndDate) +If( IncludeLastDay, 1)), 

            // Weekday() returns the day number of the week, Monday 1, Sunday 7
            Weekday( DateAdd( DatePicker1_2, Value), StartOfWeek.Monday))
    },
        // Use CountIf() to count how many days are weekdays
        // Value is the weekday number
        // 'in' returns true if the Value is in our list
        // 1-5 is Monday to Friday
        
        CountIf( WeekDays, Value in [1,2,3,4,5])
    )
)

[Radoslavov]

Thank you @timl it's working as charm.

Thank you for your help

[Chris-D]

Hi @Radoslavov, I wasn't happy with my solution so I revisited this. I also tested it against @timl's solution. Unfortunately timl's solution fails. 

My original code uses a loop and a lookup which isn't very efficient so I rewrote it without this. 

Here's my updated solution:

// Use With() to set up some input parameters
With({
    StartDate: DatePicker1_2.SelectedDate,
    EndDate: DatePicker1_3.SelectedDate
},
    With({
        // Make sure our dates are the right way around
        PastDate: Min( StartDate, EndDate),
        FutureDate: Max( StartDate, EndDate)
    },
        With({
            // Work out total days, add 1 to include the last day
            Days: DateDiff( PastDate, FutureDate) +1,

            // Work out how many working days in the first week and last week
            // This corrects for partial weeks in range
            WorkingDaysBefore: Max( 0, 8- Weekday( PastDate, StartOfWeek.Monday) -2),
            WorkingDaysAfter: Min( 5, Weekday( FutureDate, StartOfWeek.Monday))
        },
            With({
                // Int() removes numbers after the decimal point
                // This gives us the number of whole weeks, less the days we already counted
                Weeks: Int((Days -WorkingDaysBefore -WorkingDaysAfter) /7)
            },
                // The answer is the weekdays in whole weeks
                // Add on the weekdays in the partial weeks before and after
                (Weeks *5) +WorkingDaysBefore +WorkingDaysAfter
            )
        )
    )
)

Testing:

Monday to Sunday:

Single day:

Long weekend Friday to Monday:

Weekend, Saturday to Sunday:

2 weeks Sunday to Sunday:

2 weeks + 1 day, Saturday to Sunday:

[NZ0090]

@Chris-D Logged in just to say thank you for your solution! I've been struggling for the past couple of hours and both ChatGPT and Copilot aren't being helpful. Yours are the only one that works with all the dates I tested.