Leave request - Newrequest Screen - New balance Counter suming Company Holidays if selected in the LeaveEndDatePicker field

[Anonymous]

Hi,

Being in the "Newrequest Screen",  when we are selecting the Start an End Date of the Leave period, we have noticed that the "New balance Counter" is counting the company holiday (listed in the Excel Tab "Holidays") if this day is selected in the LeaveEndDatePicker field.

If we select next the day after the company holidays, in this case the New balance Counter is  not counting the company holiday.

Examples: Friday, April 02 is listed as company holidays in our excel database (Excel Tab "Holidays").

If I select the following range of time (setting up the "LeaveEndDatePicker" field on April 02), the "New balance Counter" (on th eright side) is counting 3 days insted of 2. ( because it is counting the company holiday)

If I select the following range of time (setting up the "LeaveEndDatePicker" field on April 03), the "New balance Counter" is counting 2 days, which is the expected behaviour ( only Wednesday March 31 and Thursday April 2, are working days)

Is there a way to change any formulas in order to not count a company vacation when seleted as Leave end date?

Thanks

Matth

[v-bofeng-msft]

Hi @Anonymous :

Could you tell me:

Did you replace the holidaycollection with your own excel table?

If so---------

First of all,let me explain why you encountered this problem.

The reason why you met this problem is that:

• Excel save time as UTC time by default.
• PowerApps load time as local time by default.

This picture explains the principle and solution of the error

I think this link will help you a lot:

Date Picker Sends Wrong Date 

On your side ,you may set the LeaveStartDatePicker's OnSelect property to:

//**** Note: I have marked the areas that need to be modified in RED****

If(LeaveStartDatePicker.SelectedDate <= LeaveEndDatePicker.SelectedDate,
Set(_inclusiveTotalDaysRequested, DateDiff(LeaveStartDatePicker.SelectedDate, LeaveEndDatePicker.SelectedDate, Days) + 1);
Set(_numFullWeeks, RoundDown(_inclusiveTotalDaysRequested / 7, 0));
Set(_numFullDaysPartialWeek, _inclusiveTotalDaysRequested - _numFullWeeks * 7);
Concurrent(Set(_startWeekday, Weekday(LeaveStartDatePicker.SelectedDate)), Set(_endWeekday, Weekday(LeaveEndDatePicker.SelectedDate)));
//calculates the number of business days in the partial week left over after whole weeks are subtracted out of total days requested
If(_numFullDaysPartialWeek = 6,
If(_startWeekday <= 2, Set(_numPartialWeekdays, 5), Set(_numPartialWeekdays, 4)
),
_numFullDaysPartialWeek = 5,
If(_startWeekday = 2, Set(_numPartialWeekdays, 5), _startWeekday = 1 || _startWeekday = 3 || _startWeekday = 4, Set(_numPartialWeekdays, 4), Set(_numPartialWeekdays, 3)
),
_numFullDaysPartialWeek = 4,
If(_startWeekday = 2 || _startWeekday = 3, Set(_numPartialWeekdays, 4), _startWeekday = 1 || _startWeekday = 4, Set(_numPartialWeekdays, 3), Set(_numPartialWeekdays, 2)
),
_numFullDaysPartialWeek = 3,
If(_startWeekday = 6 || _startWeekday = 7, Set(_numPartialWeekdays, 1), _startWeekday = 1 || _startWeekday = 5, Set(_numPartialWeekdays, 2), Set(_numPartialWeekdays, 3)
),
_numFullDaysPartialWeek = 2,
If(_startWeekday = 7, Set(_numPartialWeekdays, 0), _startWeekday = 1 || _startWeekday = 6, Set(_numPartialWeekdays, 1), Set(_numPartialWeekdays, 2)),
_numFullDaysPartialWeek = 1,
If(_startWeekday = 1 || _startWeekday = 7, Set(_numPartialWeekdays, 0), Set(_numPartialWeekdays, 1)
),
_numFullDaysPartialWeek = 0, Set(_numPartialWeekdays, 0)
);
Set(_workDaysInRequest, _numFullWeeks * 5 + _numPartialWeekdays);
Set(_holidaysInRequest, CountIf(YourHolidayExcelTable, DateAdd(StartDate,TimeZoneOffset(),Minutes) >= LeaveStartDatePicker.SelectedDate, DateAdd(StartDate,TimeZoneOffset(),Minutes) <= LeaveEndDatePicker.SelectedDate));
Set(_requestedDays, _workDaysInRequest - _holidaysInRequest)
)

Best Regards,

Bof

[Anonymous]

Hi @v-bofeng-msft 

first at all, thanks for your quick answer.

I can conform that I have only updated the tab Holidays, based on the default Excel File generated by the App. (Didn't created a new one).

On the other hand, My excel Date Cell format looks like if it's configured on Local 

Is your solution still working based on thsi information?

I prefer to confirm woth you befor to modify the code.

thnaks

Matth

[v-bofeng-msft]

Hi @Anonymous :

In theory, my solution should work.

Best Regards,

Bof

[Anonymous]

Hi @v-bofeng-msft 

if for this part of the flow, you solution is working (thanks for this)... nevertheless the app code, is still summing the bank holiday request if it has been approved by the manager

Here an example:

The 12/10/2021 is listed as holidays in our Excel file:

If I request it as Employee, thanks to your solution, this day is not counted anymore in the "New balance Counter".

Nevertheless, on the "GroupRequestDetails" section, from a manager View and/or ane Employee point, I can noticed that the App is still counting the Day ("GalleryRequestDetails")

And If I "Approve" this leave request (as manager), finally this days is substracted from the Employee initial amount (Excel Column "Vacation") and added as additional unit in "VacationUsed".

How can we prevent this flow. The idea is to not substract any day listed in the Holiday Tab (excel) despite it has been selected and/or approved.

Thanks in adavance

Matth

[v-bofeng-msft]

Hi @Anonymous :

Please set HomeScreen-GalleryRequests-OnSelect to

Set(_selectedLeaveItem, ThisItem);

If(_managerView,
//If manager view, get user's leave balance record and determine any overlaps this request has from other people I manage
Set(_requesterBalanceRecord, LookUp(BalanceCollection, Year = Text(Year(Now())) && EmployeeEmail = ThisItem.Requester));
ClearCollect(RequestOverlaps, Filter(LeaveCollection, EndDate >= _selectedLeaveItem.StartDate && StartDate <= _selectedLeaveItem.EndDate
&& Requester <>_selectedLeaveItem.Requester));

If(CountRows(RequestOverlaps) > 0, Set(_requestOverlapMessage, Concat(Distinct(RequestOverlaps, Requester), Result, ", ")))
);

Concurrent(
//gather requester or approver info depending on view
If(_managerView,
Set(_requester, Office365Users.UserProfile(ThisItem.Requester)),
Set(_selectedApprover, Office365Users.UserProfile(ThisItem.Approver)); If(Office365Users.UserPhotoMetadata(_selectedApprover.Id).HasPhoto, Set(_selectedApproverPhoto, Office365Users.UserPhoto(_selectedApprover.Id)))),
//**** CALCULATION USED TO DETERMINE TOTAL WORK DAYS REQUESTED ****
Set(_inclusiveTotalDaysRequested, DateDiff(ThisItem.StartDate, ThisItem.EndDate, Days) + 1);
Set(_numFullWeeks, RoundDown(_inclusiveTotalDaysRequested / 7, 0));
Set(_numFullDaysPartialWeek, _inclusiveTotalDaysRequested - _numFullWeeks * 7);
Set(_startWeekday, Weekday(ThisItem.StartDate));
Set(_endWeekday, Weekday(ThisItem.EndDate));

//calculates the number of business days in the partial week left over after whole weeks are subtracted out of total days requested
If(_numFullDaysPartialWeek = 6,
If(_startWeekday <= 2, Set(_numPartialWeekdays, 5), Set(_numPartialWeekdays, 4)
),
_numFullDaysPartialWeek = 5,
If(_startWeekday = 2, Set(_numPartialWeekdays, 5), _startWeekday = 1 || _startWeekday = 3 || _startWeekday = 4, Set(_numPartialWeekdays, 4), Set(_numPartialWeekdays, 3)
),
_numFullDaysPartialWeek = 4,
If(_startWeekday = 2 || _startWeekday = 3, Set(_numPartialWeekdays, 4), _startWeekday = 1 || _startWeekday = 4, Set(_numPartialWeekdays, 3), Set(_numPartialWeekdays, 2)
),
_numFullDaysPartialWeek = 3,
If(_startWeekday = 6 || _startWeekday = 7, Set(_numPartialWeekdays, 1), _startWeekday = 1 || _startWeekday = 5, Set(_numPartialWeekdays, 2), Set(_numPartialWeekdays, 3)
),
_numFullDaysPartialWeek = 2,
If(_startWeekday = 7, Set(_numPartialWeekdays, 0), _startWeekday = 1 || _startWeekday = 6, Set(_numPartialWeekdays, 1), Set(_numPartialWeekdays, 2)),
_numFullDaysPartialWeek = 1,
If(_startWeekday = 1 || _startWeekday = 7, Set(_numPartialWeekdays, 0), Set(_numPartialWeekdays, 1)
),
_numFullDaysPartialWeek = 0, Set(_numPartialWeekdays, 0)
);
Set(_workDaysInRequest, _numFullWeeks * 5 + _numPartialWeekdays),
Set(_holidaysInRequest, CountIf(YourHolidayExcelTable, DateAdd(StartDate,TimeZoneOffset(),Minutes) >= ThisItem.StartDate, DateAdd(StartDate,TimeZoneOffset(),Minutes)<= ThisItem.EndDate))
);
Set(_requestedDays, _workDaysInRequest - _holidaysInRequest);
//**** END CALCULATION ****
Set(_showDetails, true);

In addition, you may need to do similar operations in multiple places.

Best Regards,

Bof

[Anonymous]

Hi @v-bofeng-msft 

Sorry to be so challenging, but I noticed the following problem:

With or without the code modification you mentioned, I noticed that only for 2 specifics company holidasy listed on the excel "Holidays" Tab, the "counters" are not counting these days:

02/04/2021

12/10/2021

Sadly for the rest of Compnay holidays listed on the excel File, the "counters" are counting the days:

06/12/2021

24/12/2021
31/12/2021

(it's also happening in case of the 01/01/2021 & 06/2021)

I checked my excel file date format configuration as well as my excel Regional Format settings and everyhing looks ok:

Last check I made: Date time zone properties of the date picker control is “Local”

Any idea to explain what is happening?

Regards,

Matthieu

[Anonymous]

@v-bofeng-msft 

Additonnally I am noticing that the date displayed on the excel File is no the one I have selected on the App.

When I selected 07/04/2021 on the app, the log registered on the excel file is showing 06/04/2021

When I selected 12/10/2021 on the app, the log registered on the excel file is showing 11/10/2021

[Anonymous]

Hi @v-bofeng-msft ,

making some addtional test, I noticed that:

1/ Depsite I configure the excel StartDate & EndDate as below:

Anytme a Leave request is recorded on the excel file, the Cell format come back to :

I don't know if the information owned in the link you gave me (Date Picker Sends Wrong Date ) can help to fix this issue.

If it's the case, I need to undersatnd what do you mean with "Set the Default of the Date DATA CARD". Where do I found it? 

It looks like something you dealt with in another post (https://powerusers.microsoft.com/t5/Building-Power-Apps/Date-Format-changes-in-excel/td-p/670251)