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.

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

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

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

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