Microsoft Office Forums - View Single Post - [Solved] SumIFS statement (not sure if it is right use)

sweetcuda · 08-12-2013, 10:35 AM

Quote:

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?

BobBridges,
I am thinking that (of course I am going to go with the most complicated option) option c) As 42 hours, that is, 18 for Monday and Tuesday and 8 (projected hours) for each day that hasn't been posted yet? is the way I would like to go although I would like to complicate it even more if possible and is there a way to instead of making the work week monday through friday.... make it monday through thursday and 10 hour days. LOL.... I am sure I am testing some excel skills here. Oh I wish I were a programmer and had a programmers mind. Most of the employees work 10 hour days the only person that does not is my boss who we would project 8 hour days although if it is an issue I can work with the 8 hour days monday through friday. as long as I get the 40 hours that is really all that matters the only time it will come into play is the week where there are some hours that have already been worked.

I wonder if maybe I should just ignore those hours for the week and just go with the 40 then but I like to have up real time information at least up to the day information but maybe week information is better.