Calculating business days

%3CLINGO-SUB%20id%3D%22lingo-sub-2046727%22%20slang%3D%22en-US%22%3ECalculating%20business%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046727%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20new%20to%20excel%20but%20need%20to%20calculate%20business%20days%20excluding%20weekends%20and%20bank%20holidays%20from%20time%20received%20rather%20than%20the%20full%20start%20and%20end%20date.%20I.e%20if%20the%20start%20time%20is%2028-03-2019%2015%3A30%20it%20will%20finish%20on%2029-03-2019%2015%3A29.%20Any%20assistance%20would%20be%20much%20appreciated.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2046727%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046742%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20business%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923845%22%20target%3D%22_blank%22%3E%40LC34VAN%3C%2FA%3E%26nbsp%3BYou%20may%20look%20into%20functions%20NETWORKDAYS%20and%2For%26nbsp%3BNETWORKDAYS.INTL%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046747%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20business%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks%20I%20have%20tried%20those%20but%20they%20return%202%20full%20days.%20I%20need%20to%20be%20able%20to%20start%20the%20day%20part%20way%20through%20and%20then%20calculate%2024%20hours%20from%20that%20time%20to%20the%20next%20business%20day%20so%20it%E2%80%99s%20calculated%20as%201%20day%20rather%20than%202.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I am new to excel but need to calculate business days excluding weekends and bank holidays from time received rather than the full start and end date. I.e if the start time is 28-03-2019 15:30 it will finish on 29-03-2019 15:29. Any assistance would be much appreciated. Thanks

6 Replies

@LC34VAN You may look into functions NETWORKDAYS and/or NETWORKDAYS.INTL

@Riny_van_Eekelen Thanks I have tried those but they return 2 full days. I need to be able to start the day part way through and then calculate 24 hours from that time to the next business day so it’s calculated as 1 day rather than 2. 

@LC34VAN Okay, these functions include the starting date AND the end date. So, both the 28th and the 29th were working days, hence 2 (provided you didn't specify them as public holidays). Excel doesn't know that "your" working day ends 15:29:59 and that the start date should be excluded. But perhaps I don't understand the part of your question: "to start the day part way through".

What exactly is it that you want to achieve? Might a formula like 

=NETWORKDAYS(A1,B1)-1

... work for you?

 

@LC34VAN 

Perhaps something like

image.png

with

=NETWORKDAYS(B3,C3)-(MOD(C3,1)<MOD(B3,1))

@Sergei Baklan perfect! Thank you so much 

@LC34VAN , you are welcome