Production End Date/Time

%3CLINGO-SUB%20id%3D%22lingo-sub-2557767%22%20slang%3D%22en-US%22%3EProduction%20End%20Date%2FTime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2557767%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EIn%20my%20production%20sheet%2C%20I%20would%20like%20auto%20calculate%20end%20date%20and%20time%20based%20on%20start%20date%2Ftime%2C%20total%20required%20hours%2C%20excluding%20weekend%20and%20holiday%2C%20and%20based%20on%20working%20hours%20from%20calender%20table%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22430%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2267%22%3EMachine%3C%2FTD%3E%3CTD%20width%3D%22131%22%3ETotal%20required%20time%20(hours)%3C%2FTD%3E%3CTD%20width%3D%22116%22%3Estart%20date%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22116%22%3EEnd%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E56%3C%2FTD%3E%3CTD%3E7%2F17%2F21%2010%3A35%20AM%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3E46%3C%2FTD%3E%3CTD%3E8%2F17%2F21%2011%3A34%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%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%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%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ECalendar%20table%3C%2FP%3E%3CTABLE%20width%3D%22227%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3EDate%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EDay%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3EWorking%20Hour%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E17%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3ESat%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E18%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3ESun%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E19%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EMon%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E20%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3ETue%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E0%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E21%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EWed%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E0%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E22%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EThu%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E0%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E23%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EFri%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E0%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E24%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3ESat%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E25%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3ESun%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E26%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EMon%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E27%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3ETue%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E28%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EWed%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E29%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EThu%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E30%2F07%2F2021%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2267%22%3E%3CP%3EFri%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2283%22%3E%3CP%3E0%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EMMH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2557767%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-2558394%22%20slang%3D%22de-DE%22%3ESubject%3A%20Production%20End%20Date%2FTime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2558394%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1105667%22%20target%3D%22_blank%22%3E%40mmh21%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fnetworkdays-intl-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ENETWORKDAYS.%20INTL%20function%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EReturns%20the%20number%20of%20whole%20workdays%20between%20two%20dates%20using%20parameters%20to%20indicate%20which%20and%20how%20many%20days%20are%20weekend%20days.%20Weekend%20days%20and%20any%20days%20that%20are%20specified%20as%20holidays%20are%20not%20considered%20as%20workdays.%3C%2FP%3E%3CP%3ESyntax%20find%20you%20in%20the%20uper%20Link.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fnetworkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ENETWORKDAYS%20function%3C%2FA%3E%3C%2FP%3E%3CP%3EReturns%20the%20number%20of%20whole%20working%20days%20between%20start_date%20and%20end_date.%20Working%20days%20exclude%20weekends%20and%20any%20dates%20identified%20in%20holidays.%20Use%20NETWORKDAYS%20to%20calculate%20employee%20benefits%20that%20accrue%20based%20on%20the%20number%20of%20days%20worked%20during%20a%20specific%20term.%3C%2FP%3E%3CP%3ESyntax%20find%20you%20in%20the%20uper%20Link.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20any%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

In my production sheet, I would like auto calculate end date and time based on start date/time, total required hours, excluding weekend and holiday, and based on working hours from calender table:

 

MachineTotal required time (hours)start date End Date
A567/17/21 10:35 AM?
B468/17/21 11:34?
    
    
    

Calendar table

Date

Day

Working Hour

17/07/2021

Sat

8

18/07/2021

Sun

8

19/07/2021

Mon

8

20/07/2021

Tue

0

21/07/2021

Wed

0

22/07/2021

Thu

0

23/07/2021

Fri

0

24/07/2021

Sat

8

25/07/2021

Sun

8

26/07/2021

Mon

8

27/07/2021

Tue

8

28/07/2021

Wed

8

29/07/2021

Thu

8

30/07/2021

Fri

0

Thanks

MMH

1 Reply

@mmh21 

 

NETWORKDAYS.INTL function

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.

Syntax find you in the uper Link.

 

NETWORKDAYS function

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

Syntax find you in the uper Link.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.