How can I make Excel recognize a time period

[Michel]

I am building a shift schedule with 3 shifts of 8 hours each. (Days, Swings
and Nights) Days shift works from 7am-3pm, swings 3pm-11pm and nights from
11pm-7am. At the bottom of each shifts, I am required to count how many
employees working that day. In some instances, an employee may work an
additional 4 hours (3am-3pm, 11am-11pm, 3pm-3am etc..)

Below the Day shift, I need to count how many employee are working between
7AM and 3PM. If that employee works an additional 4 hours, that value needs
to be included under the Swing Shift as (0.5).
Somehow, Excel needs to know that lets say between the hours of 7am and 3pm
it belongs to day shift.


Currently all the employees are on one column and each column after that
represent the start and stop time (7am-3pm) for a given day.

 

[oli merge]

Could you clarify something for me - i think i can do this but need to check
I understand you correctly.

Are you saying you want to count the number of employees that have 7.00am in
the start time column and the 3.00pm or greater in the finish column, adding
the value 0.5 to a 'swing shift' cell??

If you tell me more about this swing shift cell i might be able to help.

thanks

 

[Michel]

thank you for your quick response.

Each number of employees will be on a separate cell.(Days, Swing and Nights)
And that number will be located underneath each shift. Below is an example of
the layout.

A B C
1 days
2 employee 7 AM 3 PM
3 employee 7 AM 3 PM
4 employee 7 AM 3 PM
5 employee 3 AM 3 PM
6 employee 7 AM 3 PM
7 employee 7 AM 3 PM
8 (Amount of employees)
9
10 Swings
11 employee 3 PM 11 PM
12 employee 3 PM 11 PM
13 employee 11 PM 11 PM
14 employee 3 PM 11 PM
15 employee 3 PM 3 AM
16 employee 7 AM 3 PM
17 (Amount of employees)
18
19 Nights
20 employee 11 PM 7 AM
21 employee 11 PM 7 AM
22 employee 7 PM 7 AM
23 employee 11 PM 7 AM
24 employee 11 PM 7 AM
25 employee 11 PM 11 AM
26 (Amount of employees)


In the above expample, an employee may work 8 hours or more. And sometimes
may work less than 8 hours. Sometimes, an employee like at cell A16 may more
on day shift during that day only.

So, for each cell that has the "Amount of employees", it needs to
differenciate between Days, Swings and Nights. If an employee occupies
7am-3pm you add 1 to cell B8. If an employee works from 3PM-3AM, you add 1 to
cell B17 and .5 to B26. And so forth..

The number of employees could be broken down into one decimal. This seems
very complicated and hoping you have a solution to my problem.

Michel

 

[oli merge]

Sry Michel, i couldnt find this question again after logging out and forgot
to get back to you.

I will have alook tommorow and let you know what results I get, hope that
isnt too late for you.