Home

returning a working date

%3CLINGO-SUB%20id%3D%22lingo-sub-393477%22%20slang%3D%22en-US%22%3Ereturning%20a%20working%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393477%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20future%20date%20with%2010%20free%20days%20given%20and%20I%20need%20a%20plan%20date%20based%20on%20future%20date%20-(minus)%2010%20free%20days%20but%20the%20returning%20plan%20date%20must%20in%20working%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-393477%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-393539%22%20slang%3D%22en-US%22%3ERe%3A%20returning%20a%20working%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393539%22%20slang%3D%22en-US%22%3EIn%20the%20counting%20of%20days%2C%20the%20first%20day%20is%20excluded%20while%20the%20last%20day%20is%20included%2C%20such%20that%20in%20your%20example%2C%2028-Apr-2019%20minus%2010%20days%20%3D%2018-Apr-2019%2C%20which%20is%20the%20tentative%20plan%20date.%20So%2C%20if%20the%20future%20date%20is%20in%20A1%2C%20the%20formula%20for%20the%20plan%20date%20in%20B1%20is%3A%3CBR%20%2F%3E%3DWORKDAY(A1-10%2C%3CBR%20%2F%3E--OR(WEEKDAY(A1-10)%3D%7B1%2C7%7D%2CCOUNTIF(Holidays%2CA1-10)%26gt%3B0)%2C%3CBR%20%2F%3EHolidays)%3CBR%20%2F%3ENote%20that%20Holidays%20refer%20to%20the%20defined%20name%20of%20the%20range%20containing%20the%20holidays.%3C%2FLINGO-BODY%3E
Highlighted
alvinchang
Occasional Visitor

I have a future date with 10 free days given and I need a plan date based on future date -(minus) 10 free days but the returning plan date must in working day.

 

1 Reply
In the counting of days, the first day is excluded while the last day is included, such that in your example, 28-Apr-2019 minus 10 days = 18-Apr-2019, which is the tentative plan date. So, if the future date is in A1, the formula for the plan date in B1 is:
=WORKDAY(A1-10,
--OR(WEEKDAY(A1-10)={1,7},COUNTIF(Holidays,A1-10)>0),
Holidays)
Note that Holidays refer to the defined name of the range containing the holidays.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies