Total Possible Hours Worked Between Dates

[xjohnson]

Hello,

Is it possible to calculate total possible hours an employee can work between dates based on shift schedule and excluding Holidays?

I have a start date/time in cell B6 and end date/time in cell C6 and would like the result in cell D6 please.

I also listed this years holidays. Can this be dynamic based on the year in cell I5?

Thank you very much for your help,
XJ

[63falcondude]

Here's how I would do this:

Put Sunday through Saturday in cells C21:C27
Use this formula in D21 then drag through D27: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B$6&":"&C$6)))=ROWS($1:1)))
Use this formula in E21 then drag through E27: =IFERROR((E10-B10-D10+C10)*24,0)
Use this formula in F21 then drag through F27: =COUNTIFS(H:H,">="&B$6,H:H,"<="&C$6,J:J,C21)

Now you can use a simple SUMPRODUCT formula in D6:
=SUMPRODUCT(D21:D27-F21:F27,E21:E27)

You can put this "helper" area wherever you'd like including on another hidden sheet.

See attached workbook.

[xjohnson]

That works thank you. I forgot to list company days off.

The Friday after Thanksgiving is one and there will be a Christmas break too.

Any ideas please?

[63falcondude]

You're welcome. Because there are 4 Sundays between 11/1/2018 and 11/29/2018.

[xjohnson]

Sorry I realized the number of Sundays and edited my previous reply.

[63falcondude]

Just saw your edit to post #3.

If you list the company days off with the holidays, the formulas will treat those days the same as holidays.

Then you can change F20 to something like "Company Days Off" or whatever works for you.

[xjohnson]

Great that is awesome thanks.

The second part of my question is to list the holidays by year is that possible?

[63falcondude]

Thanks for the rep!

I'm not sure what you mean. Do you mean if you were to change I5 to 2019, the 2019 holidays would show in columns H:J?

[xjohnson]

Yes that is what I mean. I tried adding what the company days off are for this year and your method works great.

The day after Thanksgiving is easy for it is the day after.

Our company Christmas break is determined by when Christmas day falls. I put this year's days off but next year I will have to manually enter them I guess.

[63falcondude]

I have an idea. You will have to have a list of all holidays and company days off (for all years) that you can put in a separate sheet and then pull from there. Give me a few minutes.

[xjohnson]

On another note if you can please.

How can I add up the hours in F10 thru F17? Does it have to do with formats?

[63falcondude]

I put a table of all holidays on Sheet2 with copied/bogus 2019 dates just to test with.

H7 =IFERROR(INDEX(Holidays!A:A,SMALL(IF(YEAR(Table1[Date])=$I$5,ROW(Table1[Date])),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula through J7 then down as far as needed.

Now when you change I5 to 2019, the list will automatically adjust as will the other formulas.

[63falcondude]

On another note if you can please.

How can I add up the hours in F10 thru F17? Does it have to do with formats?

F10 =IFERROR((E10-D10+C10-B10)*24,0)

Drag down through F16 and change the format to General.

[xjohnson]

I really appreciate the help this system is great!

[63falcondude]

Happy to help!

Now that we have a table with all of the company days off, I would refer to that in the F21 formula.

=COUNTIFS(Table1[Date],">="&B$6,Table1[Date],"<="&C$6,Table1[Day of Week],C21)

Without getting to deep into the why (I can if you'd like), this works better.

[63falcondude]

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.