I'm trying to put together a set of worksheets for work that show up who's
on a tea break at a given time..

The project has 3 tabs at the bottom to choose either
DUTIES,,SHIFTS,,BREAKS..

The first 2 DUTIES and SHIFTS work fine....but what i'm trying to do now is
get the BREAKS sheets to display the day in 15 minute segments and each 15
minutes display the people who are on a break at that time.

The DUTIES screen scrolls down to show each day of the week we are open (Mon
to Sat)

Row B has all the names of the staff and goes from cell 6 down to cell 31.

The breaks are shown in Time format in columns H to K and again in cells 6
to 31.

Is there a way that I can get the BREAKS sheets to check the times on the
DUTIES sheet and automatically insert them into the relevant time slots.


I can send the file if needed.

Many Thanks

Dave B

I put times in 15 minjute intervals in Column a starting on row 2. do do
this easily I used auto fill. the days of the week arre in columns B-G. the
program assume each person has same break at same time and works Monday -
Saturday.


Const Breaks = "Breaks"
Const Duties = "Duties"

Sheets(Breaks).Activate
'time is listed in 15 minute intervals in column A
'Starting in row 2
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set TimeRange = Range(Cells(2, 1), Cells(Lastrow, 1))


Sheets(Duties).Activate
Set BreakRange = Range(Cells(6, 8), Cells(31, 11))

For Each cell In BreakRange

If Not IsEmpty(cell) Then

For Each BreakTime In TimeRange

If cell.Value < _
BreakTime.Offset(rowoffset:=1, columnoffset:=0) Then

Sheets(Breaks).Activate
Set BreaksCells = Sheets(Breaks). _
Range("B" & BreakTime.Row & ":G" & BreakTime.Row)

For Each Break In BreaksCells
' Add names to Break sheet.
'More than one person can be on break at the same time
If IsEmpty(Break) Then
Break.Value = Break.Value + _
Sheets(Duties).Range("B" & cell.Row)
Else
Break.Value = Break.Value + ", " + _
Sheets(Duties).Range("B" & cell.Row)
End If
Next Break

Exit For
End If

Next BreakTime

End If

Next cell

End Sub

"DB" wrote:

I'm trying to put together a set of worksheets for work that show up who's
on a tea break at a given time..

The project has 3 tabs at the bottom to choose either
DUTIES,,SHIFTS,,BREAKS..

The first 2 DUTIES and SHIFTS work fine....but what i'm trying to do now is
get the BREAKS sheets to display the day in 15 minute segments and each 15
minutes display the people who are on a break at that time.

The DUTIES screen scrolls down to show each day of the week we are open (Mon
to Sat)

Row B has all the names of the staff and goes from cell 6 down to cell 31.

The breaks are shown in Time format in columns H to K and again in cells 6
to 31.

Is there a way that I can get the BREAKS sheets to check the times on the
DUTIES sheet and automatically insert them into the relevant time slots.


I can send the file if needed.

Many Thanks

Dave B

Joel,, thanks for helping me..

I'm a bit green on Excel so am not sure how to implement your suggestion.

Peoples breaks arent at the same time everyday - we have to vary the times
to keep things fair..

All morning breaks are scheduled to be between 9:30 and 11:15 (all are 15
minutes long)

All lunches should be 11:30 (some for 30 mins,some for 60 mins) - Part
timers get less as they are in for shorter days.

All afternoon break are scheduled to be between 14:40 and usually 16:30


I can send you my worksheet if you want to get a better idea on how it
looks.


Many Thanks again..

Dave :)




"Joel" wrote in message
...
I put times in 15 minjute intervals in Column a starting on row 2. do do
this easily I used auto fill. the days of the week arre in columns B-G.
the
program assume each person has same break at same time and works Monday -
Saturday.


Const Breaks = "Breaks"
Const Duties = "Duties"

Sheets(Breaks).Activate
'time is listed in 15 minute intervals in column A
'Starting in row 2
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set TimeRange = Range(Cells(2, 1), Cells(Lastrow, 1))


Sheets(Duties).Activate
Set BreakRange = Range(Cells(6, 8), Cells(31, 11))

For Each cell In BreakRange

If Not IsEmpty(cell) Then

For Each BreakTime In TimeRange

If cell.Value < _
BreakTime.Offset(rowoffset:=1, columnoffset:=0) Then

Sheets(Breaks).Activate
Set BreaksCells = Sheets(Breaks). _
Range("B" & BreakTime.Row & ":G" & BreakTime.Row)

For Each Break In BreaksCells
' Add names to Break sheet.
'More than one person can be on break at the same time
If IsEmpty(Break) Then
Break.Value = Break.Value + _
Sheets(Duties).Range("B" & cell.Row)
Else
Break.Value = Break.Value + ", " + _
Sheets(Duties).Range("B" & cell.Row)
End If
Next Break

Exit For
End If

Next BreakTime

End If

Next cell

End Sub

"DB" wrote:

I'm trying to put together a set of worksheets for work that show up
who's
on a tea break at a given time..

The project has 3 tabs at the bottom to choose either
DUTIES,,SHIFTS,,BREAKS..

The first 2 DUTIES and SHIFTS work fine....but what i'm trying to do now
is
get the BREAKS sheets to display the day in 15 minute segments and each
15
minutes display the people who are on a break at that time.

The DUTIES screen scrolls down to show each day of the week we are open
(Mon
to Sat)

Row B has all the names of the staff and goes from cell 6 down to cell
31.

The breaks are shown in Time format in columns H to K and again in cells
6
to 31.

Is there a way that I can get the BREAKS sheets to check the times on the
DUTIES sheet and automatically insert them into the relevant time slots.


I can send the file if needed.

Many Thanks

Dave B

There are only 4 cells in columns H - K on the duties sheet. Thatt would
only give you 4 breaks times for the week. I don't know how you are entering
the breaks.
You probably have it coded as follows
column H - morning break
column I - lunch
column j - afternoon break
column k - evening break.

You may want to put multiple lines in these column (use alt - enter). Then
put a date code in front of time
M:10:30,TU:10:00,W:9:15,Th:9:45,S:8:45

This is is more coding to seperate the times.
Easy way that would take up more cells is putting breaks in more columns.
Have 4 columns for each day of the week which would be 24 columns.

"DB" wrote:

Joel,, thanks for helping me..

I'm a bit green on Excel so am not sure how to implement your suggestion.

Peoples breaks arent at the same time everyday - we have to vary the times
to keep things fair..

All morning breaks are scheduled to be between 9:30 and 11:15 (all are 15
minutes long)

All lunches should be 11:30 (some for 30 mins,some for 60 mins) - Part
timers get less as they are in for shorter days.

All afternoon break are scheduled to be between 14:40 and usually 16:30


I can send you my worksheet if you want to get a better idea on how it
looks.


Many Thanks again..

Dave :)




"Joel" wrote in message
...
I put times in 15 minjute intervals in Column a starting on row 2. do do
this easily I used auto fill. the days of the week arre in columns B-G.
the
program assume each person has same break at same time and works Monday -
Saturday.


Const Breaks = "Breaks"
Const Duties = "Duties"

Sheets(Breaks).Activate
'time is listed in 15 minute intervals in column A
'Starting in row 2
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set TimeRange = Range(Cells(2, 1), Cells(Lastrow, 1))


Sheets(Duties).Activate
Set BreakRange = Range(Cells(6, 8), Cells(31, 11))

For Each cell In BreakRange

If Not IsEmpty(cell) Then

For Each BreakTime In TimeRange

If cell.Value < _
BreakTime.Offset(rowoffset:=1, columnoffset:=0) Then

Sheets(Breaks).Activate
Set BreaksCells = Sheets(Breaks). _
Range("B" & BreakTime.Row & ":G" & BreakTime.Row)

For Each Break In BreaksCells
' Add names to Break sheet.
'More than one person can be on break at the same time
If IsEmpty(Break) Then
Break.Value = Break.Value + _
Sheets(Duties).Range("B" & cell.Row)
Else
Break.Value = Break.Value + ", " + _
Sheets(Duties).Range("B" & cell.Row)
End If
Next Break

Exit For
End If

Next BreakTime

End If

Next cell

End Sub

"DB" wrote:

I'm trying to put together a set of worksheets for work that show up
who's
on a tea break at a given time..

The project has 3 tabs at the bottom to choose either
DUTIES,,SHIFTS,,BREAKS..

The first 2 DUTIES and SHIFTS work fine....but what i'm trying to do now
is
get the BREAKS sheets to display the day in 15 minute segments and each
15
minutes display the people who are on a break at that time.

The DUTIES screen scrolls down to show each day of the week we are open
(Mon
to Sat)

Row B has all the names of the staff and goes from cell 6 down to cell
31.

The breaks are shown in Time format in columns H to K and again in cells
6
to 31.

Is there a way that I can get the BREAKS sheets to check the times on the
DUTIES sheet and automatically insert them into the relevant time slots.


I can send the file if needed.

Many Thanks

Dave B

Many Thanks again....off to sort my file out now...

Cheers

Dave :)


"Joel" wrote in message
...
There are only 4 cells in columns H - K on the duties sheet. Thatt would
only give you 4 breaks times for the week. I don't know how you are
entering
the breaks.
You probably have it coded as follows
column H - morning break
column I - lunch
column j - afternoon break
column k - evening break.

You may want to put multiple lines in these column (use alt - enter).
Then
put a date code in front of time
M:10:30,TU:10:00,W:9:15,Th:9:45,S:8:45

This is is more coding to seperate the times.
Easy way that would take up more cells is putting breaks in more columns.
Have 4 columns for each day of the week which would be 24 columns.

"DB" wrote:

Joel,, thanks for helping me..

I'm a bit green on Excel so am not sure how to implement your suggestion.

Peoples breaks arent at the same time everyday - we have to vary the
times
to keep things fair..

All morning breaks are scheduled to be between 9:30 and 11:15 (all are 15
minutes long)

All lunches should be 11:30 (some for 30 mins,some for 60 mins) - Part
timers get less as they are in for shorter days.

All afternoon break are scheduled to be between 14:40 and usually 16:30


I can send you my worksheet if you want to get a better idea on how it
looks.


Many Thanks again..

Dave :)




"Joel" wrote in message
...
I put times in 15 minjute intervals in Column a starting on row 2. do
do
this easily I used auto fill. the days of the week arre in columns
B-G.
the
program assume each person has same break at same time and works
Monday -
Saturday.


Const Breaks = "Breaks"
Const Duties = "Duties"

Sheets(Breaks).Activate
'time is listed in 15 minute intervals in column A
'Starting in row 2
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set TimeRange = Range(Cells(2, 1), Cells(Lastrow, 1))


Sheets(Duties).Activate
Set BreakRange = Range(Cells(6, 8), Cells(31, 11))

For Each cell In BreakRange

If Not IsEmpty(cell) Then

For Each BreakTime In TimeRange

If cell.Value < _
BreakTime.Offset(rowoffset:=1, columnoffset:=0) Then

Sheets(Breaks).Activate
Set BreaksCells = Sheets(Breaks). _
Range("B" & BreakTime.Row & ":G" & BreakTime.Row)

For Each Break In BreaksCells
' Add names to Break sheet.
'More than one person can be on break at the same time
If IsEmpty(Break) Then
Break.Value = Break.Value + _
Sheets(Duties).Range("B" & cell.Row)
Else
Break.Value = Break.Value + ", " + _
Sheets(Duties).Range("B" & cell.Row)
End If
Next Break

Exit For
End If

Next BreakTime

End If

Next cell

End Sub

"DB" wrote:

I'm trying to put together a set of worksheets for work that show up
who's
on a tea break at a given time..

The project has 3 tabs at the bottom to choose either
DUTIES,,SHIFTS,,BREAKS..

The first 2 DUTIES and SHIFTS work fine....but what i'm trying to do
now
is
get the BREAKS sheets to display the day in 15 minute segments and
each
15
minutes display the people who are on a break at that time.

The DUTIES screen scrolls down to show each day of the week we are
open
(Mon
to Sat)

Row B has all the names of the staff and goes from cell 6 down to cell
31.

The breaks are shown in Time format in columns H to K and again in
cells
6
to 31.

Is there a way that I can get the BREAKS sheets to check the times on
the
DUTIES sheet and automatically insert them into the relevant time
slots.


I can send the file if needed.

Many Thanks

Dave B