SumIFS statement (not sure if it is right use)

[sweetcuda]

I have a worksheet that tracks labor hours imported from timesheets. It has columns for the date, employee, hours and hours type. these fields are imported. Then I have columns where each week period there is a calculation to calculate the total hours for each individual for that week period. That looks like this....



=SUMIFS(C:C,B:B,"Smith",D, "REG",A:A,">="&DATEVALUE("8/10/2013"),A:A,"<="&DATEVALUE("8/16/2013"),A:A,">0")

What I need to do is for the months days that have not passed yet I need to project out hours. I was hoping I could just add on to this statement Today into it and somehow have it calculate remaining hours (working days) until the end of the contract say 9/20/2013. DOes anyone have any ideas how I can do this wihtout having to add anymore unecessary data to the spreadsheet.

[BobBridges]

If I follow this, you're saying that your worksheet calculates hours correctly up to this date, up to TODAY() I mean, but now you want it to calculate how many hours are left. I see two possibilities:

1) If your contract has (say) 2000 hours on it, and your worksheet shows in F23 that so far 823 hours have been expended, then the hours remaining until the end of the contract are simply 2000 - F23 = 1177.

2) If you want to calculate the working hours remaining between today and 2013-09-20, then the number of hours worked so far doesn't come into it at all; it's just a matter of figuring out how many working days there are between today and 09-20 and multiplying by 8-or-whatever. As I recall, Excel has some functions that handle working hours and days in an add-on. Let's see....here it is; NETWORKDAYS "returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays." But this doesn't say anything about having to install an add-on; maybe they've made it standard in the latest versions of Excel. (I only recently migrated from 2003 to 2010.)

[sweetcuda]

Thank you BobBridges for the response. It calculates the total hours for the date range that I put in the formula and is pulling the hours from the imported information from the timesheet. What I was hoping to do was maybe to figure out if I could add a part into the formula where it says that if the day is today or later to calculate the working hours between the date range in the formula and put that total in the field.

So say I have a date range of 9/1/2013 through 9/6/2013 this is what I typed into the formula listed below is there a way to have it calculate working hours if that date range is after todays date which today is 8/8/2013, also since there is a holiday in there I would like it to take that into account. I know I am probably asking alot. I am trying to keep the form as clean as possible. Maybe I should just make a housekeeping sheet and hide it from view. what are your thoughts.

Thanks again

Originally Posted by BobBridges

If I follow this, you're saying that your worksheet calculates hours correctly up to this date, up to TODAY() I mean, but now you want it to calculate how many hours are left. I see two possibilities:

1) If your contract has (say) 2000 hours on it, and your worksheet shows in F23 that so far 823 hours have been expended, then the hours remaining until the end of the contract are simply 2000 - F23 = 1177.

2) If you want to calculate the working hours remaining between today and 2013-09-20, then the number of hours worked so far doesn't come into it at all; it's just a matter of figuring out how many working days there are between today and 09-20 and multiplying by 8-or-whatever. As I recall, Excel has some functions that handle working hours and days in an add-on. Let's see....here it is; NETWORKDAYS "returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays." But this doesn't say anything about having to install an add-on; maybe they've made it standard in the latest versions of Excel. (I only recently migrated from 2003 to 2010.)

[Pecoflyer]

Perhaps post a sample sheet showing desired results?

[sweetcuda]

I think I have attached the sample spreadsheet. I have done up one line of what I my code looks like now and then the lower one is what I would like it to end up like after all is said and done.

[BobBridges]

Sweet, I'm looking at your example rather than your workbook. It seems clear enough: You want to be able to give Excel two dates and have it tell you how many work days are in that range, right? If so, that's what I was talking about in my first response: I think you want the NETWORKDAYS function. I've never used it, but the documentations claims it should do the job.

If that isn't what you want, I think you'll have to try again to explain.

[sweetcuda]

Yes that is sort of what I want but what I am looking for is a formula that is sort of like a if then statement or calculation that says

if it is after todays date then put however many hours is left between now until sept. 20th 2013 otherwise sum the total hours in column C:C that has the name "Jack" in column B:B that has the pay type "REG" in column D that falls under the dates 8/1/2013 in column A:A and Date 8/2/2013 in column A:A. So on and so forth for each column in my spreadsheet.

Right now I have my spreadsheet to where I manually have to go in there and and add 40 hours or however many hours are in each date range remaining and then as each date range passes I remove that amount of hours I would like to not have to do this because it leaves room for errors and leaves room for me to forget to go in and manually do something and then my $$ money numbers will be off and that is never good.

I hope that better explains what I am looking for

[BobBridges]

Ok, so you need three things. The first you had before we started:

I have columns where each week period there is a calculation to calculate the total hours for each individual for that week period. That looks like this....

=SUMIFS(C:C,B:B,"Smith",D:D, "REG",A:A,">="&DATEVALUE("8/10/2013"),A:A,"<="&DATEVALUE("8/16/2013"),A:A,">0")

The second is the function I found for you:

....put however many hours is left between now until sept. 20th 2013....

That's the NETWORKDAYS function. I've never used it myself, but you shouldn't have any trouble figuring it out from the documentation.

The only part that's left, if I understand you correctly, is an IF function to decide which value to display. You know how IF works, right? You just need to say =IF(cell>TODAY(),NETWORKDAYS(arguments),SUMIFS(arguments)). Or of course you can put =NETWORKDAYS and =SUMIFS in separate helping columns and then have your IF function refer to them there.

[sweetcuda]

Ah!!! I was just doing it backwards... I was trying to put the If statement in my SumIFS statement. I will try this and see if I can get it to work. I have been having trouble with the syntax (which is always my problem) I do not have a very logical mind and am so thankful for these websites where people like you are so helpful.

Can the =If(cell>TODAY()
look at a date range that is in just one cell? that is what is in one cell or should I break those into seperate cells as a start date and end date of the date ranges?

The problem is I cannot change the format too much on the spreadsheet because it is my bosses and I am trying to keep it as close to his format as possible so that he can look at it an understand what is going on.

Originally Posted by BobBridges

Ok, so you need three things. The first you had before we started:The second is the function I found for you:That's the NETWORKDAYS function. I've never used it myself, but you shouldn't have any trouble figuring it out from the documentation.

The only part that's left, if I understand you correctly, is an IF function to decide which value to display. You know how IF works, right? You just need to say =IF(cell>TODAY(),NETWORKDAYS(arguments),SUMIFS(arguments)). Or of course you can put =NETWORKDAYS and =SUMIFS in separate helping columns and then have your IF function refer to them there.

[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.

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?

[sweetcuda]

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.

[BobBridges]

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

=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.

[sweetcuda]

Originally Posted by BobBridges

The formula I used for weekly calculations is

=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.

I have used your calculation above... but am trying to figure out how it works. It works wonderfully as long as the employee doesn't end up working on the weekend or that Friday which I had happen this last week. I tried using the evaluate formula tool and see what was going on in there and I can see the math that is going on but I guess I am wondering where the -6 is coming into play and where the -4.

I have attached the updated sample spreadsheet with hours in it and I put in there how I think maybe a workaround for the holiday could work since it is only for predicting the future holiday. I am sure if I understood the math I could put in there to where it would just have one less day in where the holiday falls but I am not sure how the math is working. I am sorry if this seems like grade school level. sometimes I get overwhelmed by the big picture and cannot see the small steps to get to the big picture.

Thank you

[BobBridges]

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:

=SUMIFS(C3,C2,RC6,C4, "REG",C1,">="&R2C-6,C1,"<="&R2C)+MAX(R2C-MAX(TODAY(),R2C-4),0)*10

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:

R2C-MAX(TODAY(),R2C-4)

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:

MIN(R2C-TODAY(),4)

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

MAX(MIN(R2C-TODAY(),4),0)

...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.