Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or

# WORKDAY - function

• Tiit Perve
• Topic Author
• Offline
• New Member
3 months 6 days ago #1511 by Tiit Perve
WORKDAY - function was created by Tiit Perve
How to get cells in excel formula (H6; H7; H8; H9)
Forma C2 = =WORKDAY(B2;MAX(ROUNDUP((E2-IF((COUNTIF(\$J\$4:\$J\$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>\$H\$3;0;IF(MOD(B2;1)>=\$H\$5;(H3-MOD(B2;1))*24;(\$H\$3-MAX(MOD(B2;1);\$H\$2)-(\$H\$5-MAX(MOD(B2;1);\$H\$4)))*24));0))/((\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24);0);0);\$J\$4:\$J\$91)+\$H\$2+(IFERROR(CEILING(MOD((E2-IF((COUNTIF(\$J\$4:\$J\$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>\$H\$3;0;IF(MOD(B2;1)>=\$H\$5;(\$H\$3-MOD(B2;1))*24;(\$H\$3-MAX(MOD(B2;1);\$H\$2)-(\$H\$5-MAX(MOD(B2;1);\$H\$4)))*24));0))/((\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24);1)-0,00000001;0,00000001);1)*(\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24+(IFERROR(CEILING(MOD((E2-IF((COUNTIF(\$J\$4:\$J\$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>H3;0;IF(MOD(B2;1)>=\$H\$5;(\$H\$3-MOD(B2;1))*24;(\$H\$3-MAX(MOD(B2;1);\$H\$2)-(\$H\$5-MAX(MOD(B2;1);\$H\$4)))*24));0))/((\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24);1)-0,00000001;0,00000001);1)*(\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24>(\$H\$4-\$H\$2)*24)*(\$H\$5-\$H\$4)*24)/24

and C3 = =WORKDAY(B3;MAX(ROUNDUP((E3-IF((COUNTIF(\$J\$4:\$J\$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>\$H\$3;0;IF(MOD(B3;1)>=\$H\$5;(H4-MOD(B3;1))*24;(\$H\$3-MAX(MOD(B3;1);\$H\$2)-(\$H\$5-MAX(MOD(B3;1);\$H\$4)))*24));0))/((\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24);0);0);\$J\$4:\$J\$91)+\$H\$2+(IFERROR(CEILING(MOD((E3-IF((COUNTIF(\$J\$4:\$J\$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>\$H\$3;0;IF(MOD(B3;1)>=\$H\$5;(\$H\$3-MOD(B3;1))*24;(\$H\$3-MAX(MOD(B3;1);\$H\$2)-(\$H\$5-MAX(MOD(B3;1);\$H\$4)))*24));0))/((\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24);1)-0,00000001;0,00000001);1)*(\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24+(IFERROR(CEILING(MOD((E3-IF((COUNTIF(\$J\$4:\$J\$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>H4;0;IF(MOD(B3;1)>=\$H\$5;(\$H\$3-MOD(B3;1))*24;(\$H\$3-MAX(MOD(B3;1);\$H\$2)-(\$H\$5-MAX(MOD(B3;1);\$H\$4)))*24));0))/((\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24);1)-0,00000001;0,00000001);1)*(\$H\$3-\$H\$2-(\$H\$5-\$H\$4))*24>(\$H\$4-\$H\$2)*24)*(\$H\$5-\$H\$4)*24)/24
But the correct value is in D2 and D3. How to get it?