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

or

WORKDAY - function

  • Tiit Perve
  • Topic Author
  • Offline
  • New Member
  • Nieuw lid
More
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?
Attachments:

Please Log in or Create an account to join the conversation.

  • Not Allowed: to create new topic.
  • Not Allowed: to reply.
  • Not Allowed: to edit your message.
Moderators: jaychivoQiu hongkunJay Chivo