09-02-2019 02:56 AM
09-02-2019 02:56 AM
I have data with different leave start date and while applying workday.int functions with holiday provided with different out put please guide as per below table.
=WORKDAY.INT(LevaeStartDate,LeaveDays,16,Holidays)-1 is qual to LeaveEnddate
|WorkerID||LeaveStartDate||Holidays||LeveDays Without Holiday and Friday|
|7||11-Feb-19||12-Feb-19||15||1-Mar-19||28-Feb-19||Wrong Output date||18|
|1011||5-Jun-19||4-Jun-19||6||12-Jun-19||13-Jun-19||Wrong Output date||9|
|1013||18-Apr-19||5-Jun-19||2||20-Apr-19||20-Apr-19||Correct output date||3|
09-02-2019 03:36 AM - edited 09-02-2019 03:46 AM
Although I do not clearly understand where the problem is, would be better also to see an Excel sample file, however, here is a link to a complete guide to Date Math Functions, including the Workday.Intl function
The weekend argument 16 is for Friday Off
Hope that helps
09-02-2019 03:45 AM
Minus one day at the end of the formula looks incorrect. For example, for ID=7 WORKDAY.INT goes on 02 March. Minus one day is 01 March, but that's Friday, thus one more day shall be deducted.
IMHO, more correct will be to use LeaveDays-1 within the formula - to the first day of leave you add total days of leaves minus 1.
For the next record, ID=1011, LeaveStartDate is 05 June, but that day is holiday. Usually LeaveStartDate is considered as first workday on leave period. If that's not your case, when you shall check if LeaveStartDate is holiday or weekend, and deduct one day depends on that.
09-02-2019 04:53 AM
Thanking you very much and I understand the incorrect formula. So I have to provide one more conditions like if leave start date is holiday should add one day additional. And also if leaves end with holiday need to deduct one more day.
Thanking you very much foe your guidance.
by Alan Skelly on June 01, 2020
by PW0105 on May 30, 2020
by Prash Shirolkar on September 25, 2018
by Johnnie Thomas on May 10, 2018