Quote:
Originally Posted by BobBridges
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.
|
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