Home

Workday.Int Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-833175%22%20slang%3D%22en-US%22%3EWorkday.Int%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833175%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20data%20with%20different%20leave%20start%20date%20and%20while%20applying%20workday.int%20functions%20with%20holiday%20provided%20with%20different%20out%20put%20please%20guide%20as%20per%20below%20table.%3C%2FP%3E%3CP%3E%3DWORKDAY.INT(LevaeStartDate%2CLeaveDays%2C16%2CHolidays)-1%20is%20qual%20to%20LeaveEnddate%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EWorkerID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ELeaveStartDate%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EHolidays%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ELeveDays%20Without%20Holiday%20and%20Friday%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EFormula%20Result%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ELeaveEndDate%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EActualEndDate%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E(Manually%20Calculated)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3ERemaks%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ETotal%20Days%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E11-Feb-19%3C%2FTD%3E%3CTD%3E12-Feb-19%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E1-Mar-19%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E28-Feb-19%3C%2FTD%3E%3CTD%3EWrong%20Output%20date%3C%2FTD%3E%3CTD%3E18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1011%3C%2FTD%3E%3CTD%3E5-Jun-19%3C%2FTD%3E%3CTD%3E4-Jun-19%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E12-Jun-19%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E13-Jun-19%3C%2FTD%3E%3CTD%3EWrong%20Output%20date%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1013%3C%2FTD%3E%3CTD%3E18-Apr-19%3C%2FTD%3E%3CTD%3E5-Jun-19%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E20-Apr-19%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E20-Apr-19%3C%2FTD%3E%3CTD%3ECorrect%20output%20date%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1017%3C%2FTD%3E%3CTD%3E11-Jul-19%3C%2FTD%3E%3CTD%3E6-Jun-19%3C%2FTD%3E%3CTD%3E26%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E14-Aug-19%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E14-Aug-19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E35%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1028%3C%2FTD%3E%3CTD%3E3-Jun-19%3C%2FTD%3E%3CTD%3E11-Aug-19%3C%2FTD%3E%3CTD%3E19%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E28-Jun-19%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E27-Jun-19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E12-Aug-19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E13-Aug-19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E14-Aug-19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-833175%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-833224%22%20slang%3D%22en-US%22%3ERe%3A%20Workday.Int%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402004%22%20target%3D%22_blank%22%3E%40mhmharis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMinus%20one%20day%20at%20the%20end%20of%20the%20formula%20looks%20incorrect.%20For%20example%2C%20for%20ID%3D7%20WORKDAY.INT%20goes%20on%2002%20March.%20Minus%20one%20day%20is%2001%20March%2C%20but%20that's%20Friday%2C%20thus%20one%20more%20day%20shall%20be%20deducted.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20more%20correct%20will%20be%20to%20use%20LeaveDays-1%20within%20the%20formula%20-%20to%20the%20first%20day%20of%20leave%20you%20add%20total%20days%20of%20leaves%20minus%201.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20next%20record%2C%20ID%3D1011%2C%20LeaveStartDate%20is%2005%20June%2C%20but%20that%20day%20is%20holiday.%20Usually%20LeaveStartDate%20is%20considered%20as%20first%20workday%20on%20leave%20period.%20If%20that's%20not%20your%20case%2C%20when%20you%20shall%20check%20if%26nbsp%3BLeaveStartDate%20is%20holiday%20or%20weekend%2C%20and%20deduct%20one%20day%20depends%20on%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-833218%22%20slang%3D%22en-US%22%3ERe%3A%20Workday.Int%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402004%22%20target%3D%22_blank%22%3E%40mhmharis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EAlthough%20I%20do%20not%20clearly%20understand%20where%20the%20problem%20is%2C%20would%20be%20better%20also%20to%20see%20an%20Excel%20sample%20file%2C%20however%2C%20here%20is%20a%20link%20to%20a%20complete%20guide%20to%20Date%20Math%20Functions%2C%20including%20the%20Workday.Intl%20function%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DqIZxeOq-QDk%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DqIZxeOq-QDk%3C%2FA%3E%3C%2FP%3E%3CP%3EThe%20weekend%20argument%2016%20is%20for%20Friday%20Off%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-833287%22%20slang%3D%22en-US%22%3ERe%3A%20Workday.Int%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanking%20you%20very%20much%20and%20I%20understand%20the%20incorrect%20formula.%20So%20I%20have%20to%20provide%20one%20more%20conditions%20like%20if%20leave%20start%20date%20is%20holiday%20should%20add%20one%20day%20additional.%20And%20also%20if%20leaves%20end%20with%20holiday%20need%20to%20deduct%20one%20more%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanking%20you%20very%20much%20foe%20your%20guidance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-833300%22%20slang%3D%22en-US%22%3ERe%3A%20Workday.Int%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402004%22%20target%3D%22_blank%22%3E%40mhmharis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESomething%20like%20this.%20You%20may%20do%20checks%20within%20your%20formula%2C%20but%20it's%20better%20to%20play%20with%20sample%20file%20to%20be%20sure%20how%20to%20change%20it%20exactly.%3C%2FP%3E%3C%2FLINGO-BODY%3E
mhmharis
New Contributor

Hello

 

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

WorkerIDLeaveStartDateHolidays LeveDays Without Holiday and Friday

Formula Result

LeaveEndDate

ActualEndDate

(Manually Calculated)

RemaksTotal Days
711-Feb-1912-Feb-19151-Mar-1928-Feb-19Wrong Output date18
10115-Jun-194-Jun-19612-Jun-1913-Jun-19Wrong Output date9
101318-Apr-195-Jun-19220-Apr-1920-Apr-19Correct output date3
101711-Jul-196-Jun-192614-Aug-1914-Aug-19 35
10283-Jun-1911-Aug-191928-Jun-1927-Jun-19 25
  12-Aug-19     
  13-Aug-19     
  14-Aug-19     
4 Replies

@mhmharis 

Hi

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

https://www.youtube.com/watch?v=qIZxeOq-QDk

The weekend argument 16 is for Friday Off

Hope that helps

Nabil Mourad

This tutorial is a Festival of Date Formulas & Functions! With over 40 examples that will allow you to hold a firm grasp of Date Math. I also included on a separate sheet a full definition of each function with the arguments required to use them. You can Download the Exercise file and Follow along

@mhmharis 

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.

Highlighted

@Sergei Baklan 

 

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.

@mhmharis 

Something like this. You may do checks within your formula, but it's better to play with sample file to be sure how to change it exactly.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies