How to Calculate duration in Excel Excluding Weekends and public holiday

[Muneeb]

Below Excel formula works fine to find duration between two dates and times.It excludes weekends(Friday and Saturday) and Public holidays also.

A1=12/27/2016 10:30:00 AM
B1=12/29/2016 11:59:59 PM
7= Exclude weekends(Friday and Saturday)
B9:B10=Exclude Holidays mentioned on B9:B10

=NETWORKDAYS.INTL(A1,B1,7,B9:B10)-1-MOD(A1,1)+MOD(B1,1)
However I need to calculate duration where B1 is not given and i have to consider current date and and Time i-e B1=now(). I used below formula but it is not giving the correct result.

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

Any idea Pls.

 

[Chihiro]

=NETWORKDAYS.INTL(D4,NOW(),7)-1-MOD(D4,1)+IF(WEEKDAY(NOW(),1)>5,1,MOD(NOW(),1))

You need the IF() portion, since you need to add 1 (i.e. 24 hours) if last date falls on weekend, otherwise add MOD(NOW(),1).

FYI - You'd need additional check for public holidays. I'd use something like ISNUMBER(MATCH(TODAY(),holidaylist,0))