B
BFiedler
I am building a spreadsheet with an employees start time, end time and
lunch start and end times. I would like to take that information and
add up how many employees are working during a specific half hour time
block through out the day on any given day during the week.
Example:
Name Monday Start Lunch Start Lunch End Monday End Tuesday
Start...
Last, First 7:00 9:00 10:00 4:00
Last, First 8:00 11:00 12:00 5:00
Last, First 16:00 20:00 21:00 00:00
Then for each half hour on monday I would like to know how many
employees are working.
6:30 7:00 7:30 8:00 8:30 9:00 9:30
0 1 1 2 2
2 2 etc.
Here is the formula that I came up
with:=IF(AND($B$3<>"",$B$3<=R$2,$E$3>R$2,NOT(AND($C$3<=R$2,$D$3>R$2))),1,
0)
B3 Start Time
E3 End Time
C3 Lunch Start Time
D3 Lunch End Time
R2 Half Hour time block (6:30)
This formula works for day 1st and 2nd shift people..but if their start
time is at 21:00 and their end time is 07:00 it does not work....(becaue
of how the greater than and less than signs are set up)
Is there a better way to do this?
+-------------------------------------------------------------------+
|Filename: Excel Sheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3824 |
+-------------------------------------------------------------------+
lunch start and end times. I would like to take that information and
add up how many employees are working during a specific half hour time
block through out the day on any given day during the week.
Example:
Name Monday Start Lunch Start Lunch End Monday End Tuesday
Start...
Last, First 7:00 9:00 10:00 4:00
Last, First 8:00 11:00 12:00 5:00
Last, First 16:00 20:00 21:00 00:00
Then for each half hour on monday I would like to know how many
employees are working.
6:30 7:00 7:30 8:00 8:30 9:00 9:30
0 1 1 2 2
2 2 etc.
Here is the formula that I came up
with:=IF(AND($B$3<>"",$B$3<=R$2,$E$3>R$2,NOT(AND($C$3<=R$2,$D$3>R$2))),1,
0)
B3 Start Time
E3 End Time
C3 Lunch Start Time
D3 Lunch End Time
R2 Half Hour time block (6:30)
This formula works for day 1st and 2nd shift people..but if their start
time is at 21:00 and their end time is 07:00 it does not work....(becaue
of how the greater than and less than signs are set up)
Is there a better way to do this?
+-------------------------------------------------------------------+
|Filename: Excel Sheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3824 |
+-------------------------------------------------------------------+