How to Calculate duration in Excel Excluding Weekends and public holiday

Change NOW() with TODAY() in your formula.

=NETWORKDAYS.INTL(A1,TODAY(),7,B9:B10)-1-MOD(A1,1)+MOD(TODAY(),1)

Or

=NETWORKDAYS.INTL(A1;NOW(),7,B9:B10)-1-MOD(A1,1)+MOD(TODAY(),1)

Try the following formula with the cell formatted to [h]:mm:ss;@ :

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

I am glad to hear back and you are welcome. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

Hi JeteMC,

based on further testing, this formula works fine if B1<>blank, as shown Below(FigA). However if B1= Blank then value is not correct as shown in fig B

Fig A

A1 B1 C1 D1 E1
start date end date Correct Result(Days) Formula Result Comments
27/12/2016 10:30 29/12/2016 23:59 2.56249 2.56249 Result matched ,OK

Fig B
start date end date Correct Result(Days) Formula Result Comments
27/12/2016 10:30 2.56249 1.66829 D1 not matched with E1, Not OK

pls note B9:B10 = 1/1/2017

If I understand correctly you are expecting an answer of 2.5625 when B1 is blank. The following formula provides that result:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

The attached file has the formula applied to both E1 and E3. There is a value in B1 (12/27/2016 11:59:59) and B3 is empty, both E1 and E3 are displaying 2.5625
Let us know if you have any questions.

It is my mistake and I apologize. Here is the way that the formula should be written to accommodate an empty cell in column B:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

When I opened the file attached to post #16 B3 read 3.5625, however as soon as I pressed the 'Enable Editing' button it changed to the expected 2.5625 to be honest I don't know what caused that behavior. When I open the file from my hard drive, the one attached to post #15, it displays 2.5625 I would encourage you to save the file from post #15 to your hard drive, close the spreadsheet then open it again and see what value is displayed when opened from your hard drive.
Let us know if you have any questions.
Edit: It just occurred to me that it is already Monday 1/2/17 in UAE so if B3 is blank the value will be different. You could test by putting the date 1/2/17 in B10 so that the formula should still only calculate the time from 10:30 am on the 27th through 11:59:59 on the 29th.

Formula:

Please Login or Register  to view this content.

I Made slight changes in your code and now it seems working fine even when B1=blank, in 2nd part of formula, I Replaced Today() with Now() and B1 with Now().Pls check and advise as till now it is giving correct results.

I made two changes. In the part of the formula where the COUNTIFS functions are comparing the list of holidays to the present date, I changed NOW() to TODAY(). The reason being that the dates in the list of holidays do not have decimal parts so comparing them to NOW() would always yield a result of zero.
Seems to me that your formula, with the changes applied as shown below, yields the correct results.

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Looking back I think that I over complicated the portion of the formula that calculates elapsed time when an end date is present. I guess in the spirit of 'if it ain't broke don't fix it' we could just leave 'well enough alone', however I wanted to point it out. The simplification would require the assumption that the end date would not fall on a Friday, Saturday nor holiday. If that assumption is correct you may want to test the following:

Formula:

Please Login or Register  to view this content.

I hope that you have a blessed day.