Originally Posted by BobBridges

It's possible to write something that could look at the character string "8/17/13-8/23/13" and interpret it. But it would be complicated, perhaps more complicated than you want to attempt and certainly more complicated than you want to impose on your boss' workbook if it can be avoided. I have some alternatives, but first I have a question:

Say it's Wednesday. Monday and Tuesday John worked 17 hours. Today's work hasn't been posted yet. How do you want John's work displayed for this week?

a) As 17 hours, ignoring the unposted part of the current week?
b) As 40 hours, because until the week is completed you want to show only the projected (NETWORKDAYS) hours?
c) As 42 hours, that is, 18 for Monday and Tuesday and 8 (projected hours) for each day that hasn't been posted yet?
d) Some other way I haven't thought of?

One solution is to add a row for the date, so that row 2 has the From date and row 3 has the To date. Then you have a real date in one of the cells that you can compare to TODAY(). Adding one row won't change the look of the worksheet much.


I tried this and it works.... =IF(TODAY()>=P3,SUMIFS(C:C,B:B,"John",D,"REG",A: A,">="&DATEVALUE("9/14/2013"),A:A,"<="&DATEVALUE("9/20/2013"),A:A,">0"),NETWORKDAYS(P2,P3,Holidays)*8)

Of course it is the beginning of the week so I cannot see how it play into once the timesheets start rolling in during the week and how it will work with the 10 hour days vs. the 8 hour days but at least it is close approximation and worse case scenario on funding.

Or you could change the values in row 2 from "8/17/13-8/23/13" to just the week-start or -ending date. If you go with option a) above, make it the first date of the week; then your calculation would say =IF(TODAY()>=C2,SUMIFS(etc),NETWORKDAY(etc)). If you go with option b), make row 2 the week-ending date and your calculation =IF(TODAY()>C2,SUMIFS(etc),NETWORKDAY(etc)). Or you could use week-ending date even for option a), by checking TODAY()>=C2-6.

If you want a combined listing as with option c), I have a notion for something even better, but I'm still trying to think through how it would look. If you put the week-ending date in row 2, then I think instead of the IF function, you could use your formula for hours posted so far plus NETWORKDAYS between TODAY and the ending date in row 2. Wouldn't that work?