Number of employees in the appropriate time column

[Guest]

I need to indicate the # of employees working in a specific time range. E.g.
If I have 9 employees working between 0800-1100, how can I get 9 to show in
the appropriate columns ?
The time range in the a column is imported as shown ( 0800-1100).

a b c d e f g h
0700 0800 0900 1000 1100 1200
range # employees
0800-1100 9 9 9 9
0800-1000 3 3 3

Thanks,

Steve

 

[Dave R.]

Try

=IF(AND(--LEFT($A2,4)<=--C$1, --RIGHT($A2,4)>=--C$1),$B2,"")

which can be copied across and down as needed.

 

[Bob Phillips]

From the set-up of the data, I think it needs

=IF(AND(LEFT($A2,4)/2400<=C$1, RIGHT($A2,4)/2400>C$1),$B2,"")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you very much. Already it's saving a lot of time. However, a few,
hopefully minor, problems.
It's including the employees in the end time also. E.g., 0800-1100 should
not have employees in the 1100 column, as they're working 0800 until 1100,
not at 1100. Results are showing in the 1100 column also.
The other problem is that nothing is showing in any of the columns where the
time range crosses midnight or starts with midnight. Data such as 2300-0100
or 2400-0200 does not produce any results. Midnight format is 2400, however,
anything prior and including 2400 such as 2300-2400 does work, as does after
such as 0100-0300.

Thanks,

Steve

Thanks,

Steve

 

[Bob Phillips]

Thank you very much. Already it's saving a lot of time. However, a few,
hopefully minor, problems.
It's including the employees in the end time also. E.g., 0800-1100 should
not have employees in the 1100 column, as they're working 0800 until 1100,
not at 1100. Results are showing in the 1100 column also.

Change the >= to >

The other problem is that nothing is showing in any of the columns where the
time range crosses midnight or starts with midnight. Data such as 2300-0100
or 2400-0200 does not produce any results. Midnight format is 2400, however,
anything prior and including 2400 such as 2300-2400 does work, as does after
such as 0100-0300.

=IF(OR(AND(--LEFT($A3,4)<=--C$1,--RIGHT($A3,4)>--C$1),AND(--LEFT($A3,4)>--RI
GHT($A3,4),OR(--LEFT($A3,4)<=--C$1,--RIGHT($A3,4)>--C$1))),$B3,"")

 

[Guest]

Thanks again. The > was so simple, he said embarrassingly.

The formula (my actual with actual cells of the worksheet) is still not
resulting in the number of employees in the ranges across 2400.

=IF(OR(AND(--LEFT($C21,4)<=--AW$2,--RIGHT($C21,4)>--AW$2),AND(--LEFT($C21,4)>RIGHT($C21,4),OR(LEFT($C21,4)<=--AW$2,--RIGHT($C21,4)>--AW$2))),$T21,"")

Thanks,

Steve

 

[Bob Phillips]

Steve,

It worked in my test, but it might be that my layout differed from yours.

Can you post the data just the first few columns and the last few, with
examples as you did before.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Test post - I replied appx. an hour ago, but the post didn't show up yet.
Hopefully it will shortly. If not, I'll repost it.

Steve

 

[Guest]

OK, great. Here's what I have. The actual cells are in ( )
(aw2) (ax2) (ay2)
(az2)
Period Total Count 2200 2230 2300 2330
(c20)2100-2400 (t20) 1 1 1 1 1
(c21)2200-0100 (t21) 4

Here' s the formula in cell aw20:
=IF(OR(AND(--LEFT($C20,4)<=--AW$2,--RIGHT($C20,4)>--AW$2),AND(--LEFT($C20,4)>RIGHT($C20,4),OR(LEFT($C20,4)<=--AW$2,--RIGHT($C20,4)>--AW$2))),$T20,"")
It correctly produces the 1's from 2100-2300

Here's the formula for cell aw21
=IF(OR(AND(--LEFT($C21,4)<=--AW$2,--RIGHT($C21,4)>--AW$2),AND(--LEFT($C21,4)>RIGHT($C21,4),OR(LEFT($C21,4)<=--AW$2,--RIGHT($C21,4)>--AW$2))),$T21,"")
It does not produce the expected 4's from 2200-2400

Thanks again,

Steve