Hello,
I have a manpower schedule that is used to track the working hours of our employees. The top row is the week# and underneath is the Month and under the month it is split into the days of the week.
I didn't make this original file but there is a sum formula that calculates all the hours up based on what you enter. For example:
Formula:
=0.5*COUNTIF(F19:IV19,"B+.5")/8+0.25*COUNTIF(F19:IV19,"B+0.25")/8+0.75*COUNTIF(F19:IV19,"B+0.75")/8+COUNTIF(F19:IV19,"B+1")/8+1.25*COUNTIF(F19:IV19,"B+1.25")/8+1.5*COUNTIF(F19:IV19,"B+1.5")/8+1.75*COUNTIF(F19:IV19,"B+1.75")/8+2*COUNTIF(F19:IV19,"B+2")/8+2.25*COUNTIF(F19:IV19,"B+2.25")/8+2.5*COUNTIF(F19:IV19,"B+2.5")/8+2.75*COUNTIF(F19:IV19,"B+2.75")/8+3*COUNTIF(F19:IV19,"B+3")/8+3.25*COUNTIF(F19:IV19,"B+3.25")/8+3.5*COUNTIF(F19:IV19,"B+3.5")/8+3.75*COUNTIF(F19:IV19,"B+3.75")/8+4*COUNTIF(F19:IV19,"B+4")/8+4.25*COUNTIF(F19:IV19,"B+4.25")/8+4.5*COUNTIF(F19:IV19,"B+4.5")/8+4.75*COUNTIF(F19:IV19,"B+4.75")/8+5*COUNTIF(F19:IV19,"B+5")/8+5.25*COUNTIF(F19:IV19,"B+5.25")/8+5.5*COUNTIF(F19:IV19,"B+5.5")/8+5.75*COUNTIF(F19:IV19,"B+5.75")/8+6*COUNTIF(F19:IV19,"B+6")/8+6.25*COUNTIF(F19:IV19,"B+6.25")/8+6.5*COUNTIF(F19:IV19,"B+6.5")/8+6.75*COUNTIF(F19:IV19,"B+6.75")/8+7*COUNTIF(F19:IV19,"B+7")/8+7.25*COUNTIF(F19:IV19,"B+7.25")/8+7.5*COUNTIF(F19:IV19,"B+7.5")/8-COUNTIF(F19:IV19,"B-1")/8-2*COUNTIF(F19:IV19,"B-2")/8
F19-IV19 is every day in the year. This equation would be for one employee for their Banked hours. the hours would add up and convert to days so if you typed in B+5 into November 6th it would add .63 days to your total banked hours.
Now the issue is that obviously this equation is long and tedious and the character limit has been reached. You can also type in B-5 to remove banked hours from the total but there is only enough room for the first 2 hours.
I'm hoping there is some way to shorten this formula to include any hours whether it be B-10 or B+10 (even though this would never happen) into maybe 1 line. ANY help would be appreciated! Thanks
Sorry if I am not clear enough. If anyone has a question I will try to answer it as best i can.
Bookmarks