Excel calculate working hours between to dates?

Question

I have a task and I don't know how to start that.picture

So I have to calculate the working time passed between these two date. I know the NETWORKDAYS but it calculates the working days.

So my overall challenge is how to calculate only the working hours in only the working days. So to add something to the working hours the hours must be between 9 am (9:00) and 5 pm (17:00) and that day should be a workday. I also have to decide which day actually a workday for example July 3. was a not workday so I should not calculate. How to add these dates to the function or script? Moreover, I want to add more workdays eg. if there is an extra Saturday working day.

How to start this with Excel functions or with macros, is there any guide for this? Calculate minutes won't be necessary.

Eg: A case arrive in 2020.06.29. 5:00 (dawn) And the finish date is 2020.07.13 14:00

It's 4days = 4*8h = 32h (Because the table knows that Friday was not a working day) (from 9 am - 5 pm) plus from 9 am to 2 pm its 5 hours So overall work time passed from the above date is: 37 hours

Please give me some points in this function or VBA code or some idea?

If you use NETWORKDAYS.INTL you can customize the holidays you use and define the weekends. You can build a table of holidays and refer to that table. — Darrell H

Havard Kleven Havard Kleven · Accepted Answer · 2020-07-06T13:40:41

As @Darell H.'s comment states, you may use NETWORKDAYS.INTIL(start_date, end_date, [weekend], [holidays]).

So in your case stated above, the formal would be

=NETWORKDAYS.INTIL(44011, 44025, 1, 43895)

Where 44011 = 2020.06.29, 44025 = 2020.07.13 and 43895 = 2020.06.03.

The 1 indicates that weekends are Saturdays and Sundays. You may change this to a number that suits your needs. The date "2020.06.03" is the holiday you mentioned in your comment. You can create a table to fit all holidays, and refer to said table in the formula, rather than a single date. As the hours are always the same, I would multiply by 8h later on to get the working time in hours if need be.

If you would like to include hours in above formula, a generic formula like below may be utilized.

=(NETWORKDAYS(B5,C5)-1)*(C5-B5)+IF(NETWORKDAYS(C5,C5);MEDIAN(MOD(C5,1),C5,B5),C5)-MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),C5,B5)

Where B5 contains the start_date and C5 contains the end_date.

If holidays is desired here, it may be added in the NETWORKDAYS-formula as the in the first formula.

Excel calculate working hours between to dates?

1 Answers