calculate holiday hours in excel

Question

I have a specific issue, think I'm close to a solution in my head but can't get it to work. In excel, I have a start (D1) and an end date (E1) both with a specific hour. I want to distill the number of holiday HOURS in this range.

So e.g. I have start date "1/1/2017 06:00" and end date "2/1/2017 14:00". I know that 1/1/2017 is a public holiday so I want to see 18 (24-6) hours of this range distilled as being on a public holiday.

I thought of using the total time (E1-D1) minus the workday hours excluding holiday by using the NETWORKDAYS function to get the holiday hours but unfortunately I can't get to the right outcome using that. I have formatted my outcome cell as h:mm.

I was looking around the forum for answers but unfortunately couldn't find it for this specific item. Is there someone who knows what I should do? regards

Edit your question to include the 'close to a solution in my head' formula. Throw in some sample data as well. Heck, you might even add expected results and make this a question that someone might have an outside chance of understanding. — user4039065

barry houdini barry houdini · Accepted Answer · 2017-04-06T21:31:55

If you have holiday dates listed in H2:H10 then this formula will give you only the holiday hours between start time/date and end time/date

=E1-D1-NETWORKDAYS.INTL(D1,E1,"0000000",H2:H10)-NETWORKDAYS.INTL(E1,E1,"0000000",H2:H10)*(MOD(E1,1)-1)+NETWORKDAYS.INTL(D1,D1,"0000000",H2:H10)*MOD(D1,1)

format result cell as [h]:mm

calculate holiday hours in excel

1 Answers