I have a schedule template that allows me to see how many people I have working each hour. However, the formula breaks when I have someone working across midnight. Can someone take a look and see how I might modify this to make it work appropriately?
count workers per hour.xlsx
Ignore this post.
Last edited by JieJenn; 12-16-2015 at 05:29 PM.
Nm. I misunderstood your question.
I ended up using a helper column. See attached.
Last edited by JieJenn; 12-16-2015 at 05:50 PM.
I created a Gantt chart and used that to produce the employee count for each hour. I filled C3:U3 with the hours.
In C4 this formula was entered and filled across and down:
Formula:
=IF($A4="","",IF(AND($A4<$B4,C$3>=$A4,C$3<=$B4),1,IF(AND($A4>$B4,C$3>=$A4,C$3>=$B4),1,IF(AND($A4>$B4,C$3<=$A4,C$3<=$B4),1,""))))
C26 has this formula filled across:
Formula:=SUM(C4:C25)
B33 has this formula filled down:
Formula:=INDEX($C$26:$U$26,MATCH(A33,$C$3:$U$3,0))
In the black area of the Gantt chart are 1s and those 1s are then summed under the chart.
Last edited by newdoverman; 12-16-2015 at 06:33 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks