Work Schedule

[Neight]

ok, here's what i want have and want to do:

SUN
Name IN OUT RANK
A.X 6:00 12:00 7
B.Y 10:00 16:00 5
C.Z 12:00 20:00 4

i have another spreadsheet that has each half hour from 6:00-24:00
i want to test the IN and OUT columns to see how many people are working for that half hour and then ouput the sum of their rank for those people that are working. anyone have any ideas on how i would do this?

[mrice]

You can do it with this user defined function

Function RankSum(InRange, OutRange, RankRange, SegmentStartTime, SegmentMinutes)
For Each Cell In InRange
    StartTime = Cell
    FinishTime = Cell.Offset(0, OutRange.Column - InRange.Column)
    If StartTime <= SegmentStartTime And FinishTime >= SegmentStartTime + (SegmentMinutes / (24 * 60)) Then
        RankSum = RankSum + Cell.Offset(0, RankRange.Column - InRange.Column)
    End If
Next Cell
End Function

Add this to a VBA module.

Next to your times column type a formula such as

=RankSum($B$2:$B$4,$C$2:$C$4,$D$2:$D$4,H2,30)

[daddylonglegs]

Here's a formula approach - see attached. I've shown everything on one worksheet for simplicity but you can easily use the same approach using separate workbooks or worksheets

I've made the following assumptions:

All your shifts are within the same day, If you have shifts like 22:00 to 06:00 then you'll need a revised formula.

If you have a shift like 16:25 to 23:30 then for the half hour period 16:00 to 16:30 the whole rank will be included. If you want to exclude the rank, or even only include part of the rank then you need a revised formula.

[daddylonglegs]

To enlarge on the above...

If your header data, Name, In, Out Rank is in A1:D1 and actual data in A2:D100 then assuming a list of times as follows:

06:00 in G2, 06:30 in H2
06:30 in G3, 07:00 in H3
07:00 in G4, 07:30 in H4 etc.

then in I2 copied down use the formula

=SUMPRODUCT(--(ROUND(B$2:B$100,5)<ROUND(H2,5)),--(ROUND(C$2:C$100,5)>ROUND(G2,5)),D$2:D$100)