Working hour calculations between two dates excluding sunday, holiday and lunch break

Hi All,

I have been searching a lot to get the right formula to calculate the working hours between two dates. Below is the scenario

Working hours
Monday to Friday - 9.00 AM to 6.00 PM
Saturday - 9.00 AM to 2.00 PM

Now I wan to calculate the working hour between two days excluding Sunday, holidays and also lunch breaks ( Monday to Saturday - 1 PM to 2 PM)

Networkdays fucntion will not be useful as it excludes Saturday also.

Lets Say I have two dates

StartDate - 8/4/2016 10:00:00
EndDare - 11/4/2016 12:00:00

So total working hours - (8+4+0+3 = 15 hours)

Can someone please help me to achieve this?

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

This uses some brute force. Without the NETWORKDAYS.INTL function dealing with the holidays was challenging.

With a lookup table in K1:Q5 (start - end times against days of the week), holidays in R9:R11 and dates / times in A1:D1 this array entered formula in F1

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

The formula yields a 3 row array of 15s. The only way I could find to return one of them was to wrap an INDEX function around it.

I'm hoping someone will come along with a more elegant solution.

File attached.

Originally Posted by kush141087

Hi,

But output I need is 15, you are getting 10

I was getting 15 at my end.

What dates are in the holidays list?

Oh yes the dates in the upload are m/d/yyyy format.

I’ve had a chance to work on this some more.

These formulas work with two assumptions:

• Since the start / end dates also have start / end times it is assumed these days are neither holidays nor Sundays
• and all days between those days are assumed to be full days (except holidays and Sundays).

This has a lookup table in I1:O5. The numbers in row 1 are the WEEKDAY numbers 1-7 Sun-Sat.
The rest of the lookup table lists the start times / end times each day and the full hours for each day minus lunch.
Holidays are listed in column O.

For convenience only there is a summary list of named ranges in H11:I15. The list has no other function. By-the-way Holidays is a Dynamic Named Range (DNR). If you are not familiar with DNRs they shrink and grow automatically as dates are deleted / added.

The formulas all refer to these named ranges.

To make it easier to explain there is a formula in F1 that allowed me to break down the final mega-formula in F2 (and F3; see below) into parts.

The parts are in F10:F13. I left them there to make reviewing easier. They only affect F1. In F2 I simply replaced the cells referenced in F1 with each of those formulas. They can be deleted when you are satisfied they work if you like, but they might make future edits and debugging easier if left.

Those formulas are … from F10 to F13 all array-entered. They are:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The final mega-formula array-entered in F2 is:

Formula:

Please Login or Register  to view this content.

You can of course reduce this further combining terms and then multiplying.

Formula:

Please Login or Register  to view this content.

I tested this extensively … different start / end dates … start / end times … holidays. It works.

Someone can probably simplify / shrink this further. I have been unable to do that.
If you get a chance to upgrade I would encourage it. I was surprised to find what a problem solver MODE.MULT is. It can reduce this workday / holidays calculation, but it is not available until version 2010.

Hello po7nair and Welcome to Excel Forum.
You are replying to a thread that has been dormant for over a year and a half. Please start a new thread and provide a link to this one if it is relevant to some question/issue that you need answered/resolved.
Let us know if you have any questions.