WORKDAY.INTL Formula not excluding Sundays!
I have a formula where I need estimated completion time based on the Start Date, Work hours needed, Start and End of working hours and shift time (21 hours). I need the formula to exclude Sundays but it does not execute it. Any help is highly appreciated!
Last edited by ramilh; 12-02-2022 at 08:26 AM.
What and where is the formula?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Dear AliGW, please kindly check the attached file for the formula. I have used Work.Intl formula for the estimated time.
What output would you expect?
It seems 01/12/2022 3:40 is correct.
Since start date fell into Sun, then using tip:
=WORKDAY(StartDate -1, Number + 1,11)
Step back 1 day, then plus 1 day, to avoid weekend
Your formula:
=WORKDAY.INTL(C4,INT(H4/J4/21)+.....
should become
=WORKDAY.INTL(C4-1,1+INT(H4/J4/21)+.....
BTW, your formula shorten version with same result:
=WORKDAY.INTL(C4-1,1+INT(H4/J4/21),11)+MOD(C4,1)+(H4-INT(H4/J4/21)*21*J4)/J4/24
Quang PT
I think you are talking about NETWORKDAYS.INTL() or WORKDAY.INTL()
AFAIK something like WORK.INTL doesn't exist
Last edited by sandy666; 12-01-2022 at 03:23 AM.
I dont think so.Originally Posted by sandy666
I think you are talking about NETWORKDAYS.INTL()
He might wants to get the end date-time, excluding Sunday, with given capacity, and day with 21 hours only.
Dear Bebo02199, my mistake but you got it absolutely right! Thanks a lot again!
@bebo
re-read post#5 and title of thread
you were too quick with your post
Last edited by sandy666; 12-01-2022 at 03:28 AM.
@sandy
I am new to all these Excel Formula and Coding things and to the forum, but you are right. I corrected the mistake.
You are welcome and thanks
Continue here - your latest thread is just a follow-on from this question, and does not need a new thread, so has been closed.
For an estimated end time taking into account NO production on Sunday and the lengths of breaks, try this formula:
Disclaimer: This formula does take into account the length of the daily breaks, but does not take into account when those breaks are exactly. However, the formula takes into account exactly when it is Sunday.Formula:
=LET(nd,H4/J4/24/(1-SUM(O3:Q3)+SUM(O2:Q2)),t,WORKDAY.INTL(C4,nd,11)+MOD(nd,1)+MOD(C4,1),t+(WEEKDAY(t)=1))
For office 16 users, the LET feature is not available. They can try the following formula.Disclaimer: This formula does take into account the length of the daily breaks, but does not take into account when those breaks are exactly. However, the formula takes into account exactly when it is Sunday.Formula:=WORKDAY.INTL(C4,H4/J4/24/(1-SUM(O3:Q3)+SUM(O2:Q2)),11)+MOD(H4/J4/24/(1-SUM(O3:Q3)+SUM(O2:Q2)),1)+MOD(C4,1)+(WEEKDAY(WORKDAY.INTL(C4,H4/J4/24/(1-SUM(O3:Q3)+SUM(O2:Q2)),11)+MOD(H4/J4/24/(1-SUM(O3:Q3)+SUM(O2:Q2)),1)+MOD(C4,1))=1)
Last edited by HansDouwe; 12-02-2022 at 07:24 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
ramilh 
Register To Reply
Bookmarks