Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Number of employees in the appropriate time column
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 |
#2
|
|||
|
|||
Try =IF(AND(--LEFT($A2,4)<=--C$1, --RIGHT($A2,4)=--C$1),$B2,"") which can be copied across and down as needed. "Steve" wrote in message ... 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 |
#3
|
|||
|
|||
From the set-up of the data, I think it needs
=IF(AND(LEFT($A2,4)/2400<=C$1, RIGHT($A2,4)/2400C$1),$B2,"") -- HTH RP (remove nothere from the email address if mailing direct) "Dave R." wrote in message ... Try =IF(AND(--LEFT($A2,4)<=--C$1, --RIGHT($A2,4)=--C$1),$B2,"") which can be copied across and down as needed. "Steve" wrote in message ... 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 |
#4
|
|||
|
|||
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 "Dave R." wrote: Try =IF(AND(--LEFT($A2,4)<=--C$1, --RIGHT($A2,4)=--C$1),$B2,"") which can be copied across and down as needed. "Steve" wrote in message ... 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 |
#5
|
|||
|
|||
"Steve" wrote in message ... 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,"") |
#6
|
|||
|
|||
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" wrote: "Steve" wrote in message ... 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,"") |
#7
|
|||
|
|||
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) "Steve" wrote in message ... 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" wrote: "Steve" wrote in message ... 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,"") |
#8
|
|||
|
|||
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 "Bob Phillips" wrote: 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) "Steve" wrote in message ... 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" wrote: "Steve" wrote in message ... 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,"") |
#9
|
|||
|
|||
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 "Bob Phillips" wrote: 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) "Steve" wrote in message ... 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" wrote: "Steve" wrote in message ... 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,"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: Which numbers in a column equal a certain number (withou. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Total number of each month in a column of dates | Excel Worksheet Functions | |||
Dynamic Column VlookUps Based on Week Number | Excel Worksheet Functions | |||
How to calculate the data in excel 2002 including only the last 9. | Excel Worksheet Functions |