Need total production hrs available for 'next' month

[tmanya]

Hello All!

Using Excel 2010

I need a formula to calculate the total production hours for the next month (always next month, not just July). I have a formula that identifies the number of working days available (won't use NETWORKDAYS because we have to count the weekends).

=(EOMONTH(TODAY(),1))-(EOMONTH(TODAY(),0)+1)-(COUNTIFS(Holidays,">="&EOMONTH(TODAY(),0)+1,Holidays,"<="&EOMONTH(TODAY(),1)))+1


For July specifically, my result is 29 days (July 4&5 are holidays).

I am stumped by the fact that we don't work 24 hour every day and I don't know how accommodate that. We work 20 hours a day Mon -> Thu and 24 hours Fri -> Sun (152hrs/week). Of course, holidays are 0 hours.

Capture.JPG

Not sure if the pic will show but my data layout is such:
U3 = EOMONTH(TODAY(),0)+1 (start of the next month)
V3 = EOMONTH(TODAY(),1)+1 (start of the month after next)
U4 = (EOMONTH(TODAY(),1))-(EOMONTH(TODAY(),0)+1)-(COUNTIFS(Holidays,">="&EOMONTH(TODAY(),0)+1,Holidays,"<="&EOMONTH(TODAY(),1)))+1
V4 = (EOMONTH(TODAY(),2))-(EOMONTH(TODAY(),1)+1)-(COUNTIFS(Holidays,">="&EOMONTH(TODAY(),1)+1,Holidays,"<="&EOMONTH(TODAY(),2)))+1

Holidays = named range with our company holidays listed. July 4 & 5 for July and none for August.

I am trying to do this in one cell, without the use of helper columns or any additional tables to maintain but, if that cannot be done, then so be it.

Any advice would be greatly appreciated.

Thanks in advance,
Tanya

[Crawfinator1]

This is possible however the formula is huge, you have too many arguments to make this commercially solvable.... you need to count up the number of occurances of each day i.e st/sun/mon etc in each month, then multiply each day by its particular hours, then deduct holidays and their applicable loss of hours depending on which day they fall.

I managed to calculate this with three collumns of data in about 45 seconds. i.e. column A Date, column B day of week, column C hours depending on if mon-thur or fri-sun, then collumn D for your holidays.

Then simply on your working spreadsheet sumifs figures are in the date range.....

I assume you could probably have done this as you didn't want the helper columns - but sometimes you just need to ditch the complexity and go with the simple.

[tmanya]

Yes, I can accomplish this with helper columns. That is the route I will take then.

Thank you for taking the time to review and responding.

[daddylonglegs]

This isn't difficult to do with a single formula in Excel 2010 because you can use NETWORKDAYS.INTL to count all the days, e.g. this will give you next months total hours

=SUM(NETWORKDAYS.INTL(EOMONTH(TODAY(),0)+1,EOMONTH(TODAY(),1),{"0000111","1111000"},Holidays)*{20,24})

Format result cell as general

I get 624 assuming July 4th and 5th are holidays

[tmanya]

FANTASTIC! Thank you!

I looked at networkdays.intl but didn't think it would work. I didn't understand how I could use the string values but it makes sense seeing it.

624 is what I expected to see for July and 676 was my result for Aug (no holidays) which was also the expected value.

Thank you both very much for your time and help!