EricGoBills
New Member
- Joined
- Jun 19, 2018
- Messages
- 2
Hello,
First post on here but have used this site countless times as a reference.
I am creating a heat chart for staffing purposes to show how many people are on shift for a specific day/time.
The first sheet has schedule info (Name, Start Time, End Time, and 1/0 for each day of the week they work)
[TABLE="width: 843"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Saturday[/TD]
[TD="align: center"]Start EST[/TD]
[TD="align: center"]End EST[/TD]
[TD="align: center"]Duration[/TD]
[TD="align: center"]Shift[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6:00[/TD]
[TD="align: center"]14:30[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]13:00[/TD]
[TD="align: center"]22:00[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Jack[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]22:00[/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]C[/TD]
[/TR]
</tbody>[/TABLE]
The second sheet is where I am creating my heat chart. Using the following formula it will calculate for everyone except the employees who work the overnight. (end shift < start shift)
(formula from cell B3)
=SUMIFS(Schedule!$B:$B,Schedule!$B:$B,1,Schedule!$I:$I,"<="&B$2,Schedule!$J:$J,">"&B$2)
[TABLE="width: 1616"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="align: center"]12:30:00 AM[/TD]
[TD="align: center"]1:00:00 AM[/TD]
[TD="align: center"]1:30:00 AM[/TD]
[TD="align: center"]2:00:00 AM[/TD]
[TD="align: center"]2:30:00 AM[/TD]
[TD="align: center"]3:00:00 AM[/TD]
[TD="align: center"]3:30:00 AM[/TD]
[TD="align: center"]4:00:00 AM[/TD]
[TD="align: center"]4:30:00 AM[/TD]
[TD="align: center"]5:00:00 AM[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]Saturday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
Looking for any suggestions on how to incorporate the overnight shifts into this formula. ("Jack" in the example) I have played with IF statements checking if Start Time > End Time, but can't seem to get it to work.
First post on here but have used this site countless times as a reference.
I am creating a heat chart for staffing purposes to show how many people are on shift for a specific day/time.
The first sheet has schedule info (Name, Start Time, End Time, and 1/0 for each day of the week they work)
[TABLE="width: 843"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Saturday[/TD]
[TD="align: center"]Start EST[/TD]
[TD="align: center"]End EST[/TD]
[TD="align: center"]Duration[/TD]
[TD="align: center"]Shift[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6:00[/TD]
[TD="align: center"]14:30[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]13:00[/TD]
[TD="align: center"]22:00[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Jack[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]22:00[/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]C[/TD]
[/TR]
</tbody>[/TABLE]
The second sheet is where I am creating my heat chart. Using the following formula it will calculate for everyone except the employees who work the overnight. (end shift < start shift)
(formula from cell B3)
=SUMIFS(Schedule!$B:$B,Schedule!$B:$B,1,Schedule!$I:$I,"<="&B$2,Schedule!$J:$J,">"&B$2)
[TABLE="width: 1616"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="align: center"]12:30:00 AM[/TD]
[TD="align: center"]1:00:00 AM[/TD]
[TD="align: center"]1:30:00 AM[/TD]
[TD="align: center"]2:00:00 AM[/TD]
[TD="align: center"]2:30:00 AM[/TD]
[TD="align: center"]3:00:00 AM[/TD]
[TD="align: center"]3:30:00 AM[/TD]
[TD="align: center"]4:00:00 AM[/TD]
[TD="align: center"]4:30:00 AM[/TD]
[TD="align: center"]5:00:00 AM[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]Saturday[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
Looking for any suggestions on how to incorporate the overnight shifts into this formula. ("Jack" in the example) I have played with IF statements checking if Start Time > End Time, but can't seem to get it to work.