Answered

How to exclude just Sunday from date difference

(0) Share

Report

Posted on by cardinalnight

212

I'm working on tweaking this formula:
Solved: Excluding weekends using DateDiff and Datepicker - Power Platform Community (microsoft.com)

So it excludes Sundays from two dates. I'm currently using this:

Set(varAdd14Days, DateAdd(selectedDate, 14, TimeUnit.Days));

Set(varCalculateAmtOfDays, RoundDown(DateDiff(selectedDate, varAdd14Days, TimeUnit.Days) / 7, 0) * 5 +
 Mod(5 + Weekday(varAdd14Days) - Weekday(selectedDate), 5));

Set(varActualReturnDate, DateAdd(selectedDate, varCalculateAmtOfDays, TimeUnit.Days));

It's for a loan service. A user will book an item, they get it for 14 days, then have to return it. At the moment this formula is (apparently) excluding weekends, whereas I only want it to exclude Sundays.
The final line of the above, will show the return date to the user.
Need to exclude Sundays.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

mmbr1606 14,629 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
hey @cardinalnight

can you try this approach please:

Set(varAdd14Days, DateAdd(selectedDate, 14, TimeUnit.Days)); // Calculate the total number of days initially Set(varTotalDays, DateDiff(selectedDate, varAdd14Days, TimeUnit.Days) + 1); // Calculate the number of Sundays Set(varFirstSunday, If(Weekday(selectedDate) > 1, 8 - Weekday(selectedDate, StartOfWeek.Monday), 1)); Set(varDaysTillFirstSunday, varFirstSunday - 1); Set(varEffectiveDays, varTotalDays - varDaysTillFirstSunday); Set(varNumberOfSundays, If(varEffectiveDays >= 0, RoundDown(varEffectiveDays / 7, 0) + 1, 0)); // Calculate the actual return date excluding Sundays Set(varActualReturnDate, DateAdd(selectedDate, varTotalDays + varNumberOfSundays - 1, TimeUnit.Days));

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
cardinalnight 212 on at

Like (0)

Report
Copy link

Link copied!
Thank you. I've added to this system of weekday checks with this:
Set(varAdd14Days, DateAdd(selectedDate, 14, TimeUnit.Days)); // Calculate the total number of days initially Set(varTotalDays, DateDiff(selectedDate, varAdd14Days, TimeUnit.Days) + 1); // Calculate the number of Sundays Set(varFirstSunday, If(Weekday(selectedDate) > 1, 8 - Weekday(selectedDate, StartOfWeek.Monday), 1)); Set(varDaysTillFirstSunday, varFirstSunday - 1); Set(varEffectiveDays, varTotalDays - varDaysTillFirstSunday); Set(varNumberOfSundays, If(varEffectiveDays >= 0, RoundDown(varEffectiveDays / 7, 0) + 1, 0)); // Calculate the actual return date excluding Sundays Set(varActualReturnDate, DateAdd(selectedDate, varTotalDays + varNumberOfSundays - 1, TimeUnit.Days)); // If the return day is a Sunday, push it to Monday if not, then use varActualReturnDate: If(Weekday(varActualReturnDate) = 1, Set(varIfSunday, DateAdd(varActualReturnDate, 1,TimeUnit.Days)), Set(varIfSunday, varActualReturnDate));
Hopefully there shouldn't be any issues with my addition of the final line there. I've tested it and it works.
Hopefully this helps someone with the same challenge.

Was this reply helpful? Yes No