The reason it didn't work right for any hours posted Fridays or weekends is that I used a Fri-to-Thu week. That's just based on what you said about most people working Mon-Thu. I didn't stop to ask you about that, and maybe I should have; but it should be easy enough to fix. Before that, though, let's look at how the math works:

First, I defined Thursday as the last day of the week and put that date in R2. Then we use this formula: This adds together, for each week, two numbers: 1) this week's hours posted so far in the first four columns (that's the SUMIFS part), and 2) a projection of the hours yet to be worked, if any part of that week is still in the future (that's the MAX part).

SUMIFS:
The first two conditions, C2=RC6 and C4="REG", you already understand because you were using them yourself.

The fourth condition, C1<=R2C, is a check to omit any hours posted for a future week. R2C contains the last day of the week, so this test just checks to be sure the posting date is no greater than R2C.

The third condition, C1>=R2C-6, omits hours posted for a previous week. Take C13, for example, the week ending Thursday 08-22. That week started Friday the 16th, six days before the date in R2C13; thus I compare C1 not to R2C but to R2C-6.

MAX:
The idea here is to calculate the number of hours that will normally be worked on any part of the week that's still in the future. Basically, that's the number of days still left in the week (R2C-TODAY()) times the number of hours per day (10).

(RC2-TODAY())*10 would work fine for this week. Take a look at C14; that's the week ending Thu the 29th. R2C14-TODAY() is 3—that is, there are 3 days until Thursday, after today—and when you multiply that by 10 you get 30 more hours for the week, plus whatever hours are already posted for today. Fine so far.

But what about the calculation for the following weeks? C16 is for the week ending 09-12; in that column, RC2-TODAY() is 17 days, or 170 hours. So I told it to max out at 4 days' worth, by subtracting either TODAY() or RC2-4, whichever was greater:Now that I'm explaining this, I see that it would have been clearer, and would have worked just as well, to calculate the number of days as R2C-TODAY() or 4, whichever is less:But there's one wrinkle left: What about previous weeks? For C10, the week ending 08-01, RC2-TODAY() is -25 days, or -250 hours. So I told it to never go negative, by adding MAX(<day count>,0). If I use the clearer function, that's...Plus of course multiplying the result by 10 to convert from days to hours.

---

Now, looking at your new worksheet, I see you've tweaked things a bit. First, you've retained the two-row date system, so R2 has the week-start date and R3 the week-end date. In that case, you can modify the formula to use R2C instead of R3C-6—a minor change, but it's always nice to simplify.

Under this system, I think Saturday hours would be counted with the following week, and maybe you don't want that. If so, we still have to adjust a little; you can decide whether you want to pursue that.

If we have to settle for noting upcoming holidays manually, I think your way is about as sensible as it gets. The one thing I suppose you might do, if you wanted to go to a little extra trouble, is start a list on a separate worksheet of the dates of upcoming holidays, vacations etc, and display on the main sheet a count of the number of such items in the list times hours per day. That's probably how I'd do it. But I'm sorry we couldn't find a built-in Excel function to do the same thing. ...Although come to think of it how could we be sure that the standard list of holidays that Excel uses exactly matches the list your company uses? I mean, do you take a day off for Easter, MLK's birthday, President's Day or some special company day no one else knows about? Some do, some don't; and your way at least is certain to track only what you mean to track, not some one-size-fits-all standard.