Well, the 10-hour-day part is easy; NETWORKDAYS just tells you how many work days occur between two dates, and you multiply it by 8 to get work hours for that period. If you're using 10-hour days, you just multiply by 10 instead.
But how to tell it to figure four work days per week? The documentation says there's a special form of the function, NETWORKDAYS.INTL (I suppose the suffix means "international") that lets you tell it which days are weekends. But it turns out that just means which
one or two days are weekends, eg Sat & Sun, Thurs & Fri, Sun only, like that. You want a 3-day weekend, at least for some employees, so that's no help.
I think we're going to have to go another way. In your sample, each column represents a single week, right? So we don't need NETWORKDAYS at all, so long as you're willing to forego holiday calculations or worry about them separately; you can just project 40 hrs for any week that hasn't occurred yet. You don't even need to know whether the particular employee on that row works 4 or 5 days, or which days; it's always projected to be 40, right?
And for weeks that have been partially reported, it seems to me we can do something about that; it may just take a little thought. Certainly we can adjust it to use a four-day work week and a ten-hour day; that's a minor change in the calculation. We should probably do that even though your boss is the one exception; better design the pattern to fit the many than the one.
---
At this point I started writing some thoughts about how it might be done. That went on for several more paragraphs; then I pulled up your sample again and started experimenting. In the end I have something that I think will work—partly. Take a look at the attached Sweet.xlsx. Ignore the Hours and Weeks worksheets; I was going to suggest that you keep them on separate worksheets. I still think that's better, but you said you didn't want to change your boss' layout, which I understand.
The worksheet named "Bob's attempt" uses the four-day 10-hour assumption, which is good. It combines posted hours with projected hours, which is good. It's fairly simple, which is absolutely great. And I didn't change much about the layout—not enough that I think your boss will complain. The only real difference is that row 2 has just one date in it, the last day of the week, which is a Thursday for your purposes.
The formula I used for weekly calculations is
Code:
=SUMIFS(C3,C2,RC6,C4, "REG",C1,">="&R2C-6,C1,"<="&R2C)+MAX(R2C-MAX(TODAY(),R2C-4),0)*10
This is basically your SUMIFS function, simplified because it could use R2C for the date, plus any future project hours; that's the "MAX(R2C-MAX(TODAY(),R2C-4),0)*10" part. It's much easier to put together such a formula than it is to figure it out afterward; nevertheless I suspect this explanation is already too long, so I'll let you work at it and then ask me any questions you have. But basically it figures out how many days in this column's week are still in the future—counting today—and multiplies by 10 to get hours.
Note that if you post John's hours for the day some time during the evening of the same day, this formula will display double hours for that day, the ones he worked plus the ones projected (since it's still today). In the morning, it'll be back to showing just the posted hours.
This still leaves holidays to consider. I've a notion that we might do something by posting the upcoming holiday in the leftmost columns, maybe with 0 hours and "HOL" in the Type column. I'm not sure. If it were me, I'd probably just type "30" in that week of September to remind me. But we may think of a better way. Or you may.