Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Another scheduling question.
Hello,
I'm trying to figure out if it is possible with Excel to do this or if maybe Access would be better. I have employee names in column C and times in row 2. The times go from 8:00am - 9:00pm in 15 minute intervals. There is a 1 in each cell that the employees work. What I would like to do is set it up so that Breaks and lunches are automatically set based on some criteria. It would need to either add up the hours worked in the day or look at the total hours worked (the sum of 1's). It also needs to check the percentage offline. This is a very generic representation, but I don't know how to attach a snippet of the code. 8:00 8:15 8:30 8:45 9:00 Employee 1 1 1 1 .5 hour Employee 2 1 1 B 1 1 1 hour %offline 0 0 50% 0 0 -- If you fail to plan, then you plan to fail. |
#2
|
|||
|
|||
Assuming worked hours are always indicated by a number such as 1 (as you
show), you should be able to get the totals that you show on the right with a formula like =COUNT(B2:BA2)/4 but that gives me .75 for Employee 1, not 0.5 As for the times on break or lunch, assuming they are entered with letters instead of a numbers, =(COUNTA(B2:BA2)-COUNT(B2:BA2))/4 On Wed, 17 Nov 2004 09:15:04 -0800, "Sicarii" wrote: Hello, I'm trying to figure out if it is possible with Excel to do this or if maybe Access would be better. I have employee names in column C and times in row 2. The times go from 8:00am - 9:00pm in 15 minute intervals. There is a 1 in each cell that the employees work. What I would like to do is set it up so that Breaks and lunches are automatically set based on some criteria. It would need to either add up the hours worked in the day or look at the total hours worked (the sum of 1's). It also needs to check the percentage offline. This is a very generic representation, but I don't know how to attach a snippet of the code. 8:00 8:15 8:30 8:45 9:00 Employee 1 1 1 1 .5 hour Employee 2 1 1 B 1 1 1 hour %offline 0 0 50% 0 0 |
#3
|
|||
|
|||
The way it works right now I manually enter a "B" for a break and two "L's"
for a lunch. Then I have it total everything with: BE4=COUNTIF(D4:BC4, "B") BH4=COUNTIF(D4:BC4, "1") BJ4=(BH4+BE4)/4 What I'm trying to do is to not have to manually enter the breaks and lunches and get it to do it automatically. A person scheduled 8 hrs would get 2 breaks and a lunch, but a part-timer who works a total of 4 hrs would only need to be scheduled one break. At the same time it needs to verify the length of time between breaks per employee and also not place a break or lunch in that interval if there are too many people off on break/lunch already. My company has purchased no workforce management software and I'm basically trying to make my own. "Myrna Larson" wrote: Assuming worked hours are always indicated by a number such as 1 (as you show), you should be able to get the totals that you show on the right with a formula like =COUNT(B2:BA2)/4 but that gives me .75 for Employee 1, not 0.5 As for the times on break or lunch, assuming they are entered with letters instead of a numbers, =(COUNTA(B2:BA2)-COUNT(B2:BA2))/4 On Wed, 17 Nov 2004 09:15:04 -0800, "Sicarii" wrote: Hello, I'm trying to figure out if it is possible with Excel to do this or if maybe Access would be better. I have employee names in column C and times in row 2. The times go from 8:00am - 9:00pm in 15 minute intervals. There is a 1 in each cell that the employees work. What I would like to do is set it up so that Breaks and lunches are automatically set based on some criteria. It would need to either add up the hours worked in the day or look at the total hours worked (the sum of 1's). It also needs to check the percentage offline. This is a very generic representation, but I don't know how to attach a snippet of the code. 8:00 8:15 8:30 8:45 9:00 Employee 1 1 1 1 .5 hour Employee 2 1 1 B 1 1 1 hour %offline 0 0 50% 0 0 |
#4
|
|||
|
|||
What you are asking for cannot be accomplished with formulas. If you want the
software to schedule the breaks and lunch hours, and to do that by looking at the entire schedule as well as the hours for the particular employee, I believe that, as you have surmised, you require specialized software for scheduling. On Wed, 17 Nov 2004 13:03:05 -0800, "Sicarii" wrote: The way it works right now I manually enter a "B" for a break and two "L's" for a lunch. Then I have it total everything with: BE4=COUNTIF(D4:BC4, "B") BH4=COUNTIF(D4:BC4, "1") BJ4=(BH4+BE4)/4 What I'm trying to do is to not have to manually enter the breaks and lunches and get it to do it automatically. A person scheduled 8 hrs would get 2 breaks and a lunch, but a part-timer who works a total of 4 hrs would only need to be scheduled one break. At the same time it needs to verify the length of time between breaks per employee and also not place a break or lunch in that interval if there are too many people off on break/lunch already. My company has purchased no workforce management software and I'm basically trying to make my own. "Myrna Larson" wrote: Assuming worked hours are always indicated by a number such as 1 (as you show), you should be able to get the totals that you show on the right with a formula like =COUNT(B2:BA2)/4 but that gives me .75 for Employee 1, not 0.5 As for the times on break or lunch, assuming they are entered with letters instead of a numbers, =(COUNTA(B2:BA2)-COUNT(B2:BA2))/4 On Wed, 17 Nov 2004 09:15:04 -0800, "Sicarii" wrote: Hello, I'm trying to figure out if it is possible with Excel to do this or if maybe Access would be better. I have employee names in column C and times in row 2. The times go from 8:00am - 9:00pm in 15 minute intervals. There is a 1 in each cell that the employees work. What I would like to do is set it up so that Breaks and lunches are automatically set based on some criteria. It would need to either add up the hours worked in the day or look at the total hours worked (the sum of 1's). It also needs to check the percentage offline. This is a very generic representation, but I don't know how to attach a snippet of the code. 8:00 8:15 8:30 8:45 9:00 Employee 1 1 1 1 .5 hour Employee 2 1 1 B 1 1 1 hour %offline 0 0 50% 0 0 |
#5
|
|||
|
|||
Thanks, I have VB6 at home so I guess it's time to learn it a little bit more.
"Myrna Larson" wrote: What you are asking for cannot be accomplished with formulas. If you want the software to schedule the breaks and lunch hours, and to do that by looking at the entire schedule as well as the hours for the particular employee, I believe that, as you have surmised, you require specialized software for scheduling. On Wed, 17 Nov 2004 13:03:05 -0800, "Sicarii" wrote: The way it works right now I manually enter a "B" for a break and two "L's" for a lunch. Then I have it total everything with: BE4=COUNTIF(D4:BC4, "B") BH4=COUNTIF(D4:BC4, "1") BJ4=(BH4+BE4)/4 What I'm trying to do is to not have to manually enter the breaks and lunches and get it to do it automatically. A person scheduled 8 hrs would get 2 breaks and a lunch, but a part-timer who works a total of 4 hrs would only need to be scheduled one break. At the same time it needs to verify the length of time between breaks per employee and also not place a break or lunch in that interval if there are too many people off on break/lunch already. My company has purchased no workforce management software and I'm basically trying to make my own. "Myrna Larson" wrote: Assuming worked hours are always indicated by a number such as 1 (as you show), you should be able to get the totals that you show on the right with a formula like =COUNT(B2:BA2)/4 but that gives me .75 for Employee 1, not 0.5 As for the times on break or lunch, assuming they are entered with letters instead of a numbers, =(COUNTA(B2:BA2)-COUNT(B2:BA2))/4 On Wed, 17 Nov 2004 09:15:04 -0800, "Sicarii" wrote: Hello, I'm trying to figure out if it is possible with Excel to do this or if maybe Access would be better. I have employee names in column C and times in row 2. The times go from 8:00am - 9:00pm in 15 minute intervals. There is a 1 in each cell that the employees work. What I would like to do is set it up so that Breaks and lunches are automatically set based on some criteria. It would need to either add up the hours worked in the day or look at the total hours worked (the sum of 1's). It also needs to check the percentage offline. This is a very generic representation, but I don't know how to attach a snippet of the code. 8:00 8:15 8:30 8:45 9:00 Employee 1 1 1 1 .5 hour Employee 2 1 1 B 1 1 1 hour %offline 0 0 50% 0 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
basic pie chart question | Charts and Charting in Excel | |||
drop down list question | Excel Discussion (Misc queries) | |||
Calculation Question | Excel Worksheet Functions | |||
Have a question on scrolling sum with Excel. | Excel Worksheet Functions | |||
end of worksheet question | Excel Worksheet Functions |