cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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)

Matthieu1_0-1617185230603.png

 

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)

 

Matthieu1_1-1617185379981.png

 

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

 

Thanks

 

Matth

 

8 REPLIES 8
v-bofeng-msft
Community Support
Community Support

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

 

v-bofeng-msft_0-1617244681692.jpeg

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

1.JPG

Best Regards,

Bof

 

Anonymous
Not applicable

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 

Matthieu1_0-1617603769550.png

Is your solution still working based on thsi information?

 

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

 

thnaks

 

Matth

 

Hi @Anonymous :

In theory, my solution should work.

Best Regards,

Bof

Anonymous
Not applicable

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:

 

Matthieu1_2-1617617743608.png

 

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

 

Matthieu1_3-1617617841566.png

 

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

 

Matthieu1_0-1617617682905.png

 

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

 

 

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
Not applicable

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

 

Matthieu1_0-1617725391217.png

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

 

Matthieu1_1-1617725486964.png

(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:

Matthieu1_2-1617725672361.png

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

Matthieu1_3-1617725848565.png

 

Any idea to explain what is happening?

 

Regards,

 

Matthieu

Anonymous
Not applicable

@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.

 

Matthieu1_0-1617729622945.pngMatthieu1_4-1617729704500.png

 

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
Not applicable

Hi @v-bofeng-msft ,

 

making some addtional test, I noticed that:

 

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

Matthieu1_0-1617781026262.png

 

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

 

Matthieu1_1-1617781070171.png

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)

 

 

 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,375)