rota sheet

[smitz]

HIYA PEOPLE

I AM TRYING TO CREATE A ROTA TIME SHEET FOR WORK. I WANT TO CREATE I
SO THAT WHEN THE HOURS THAT ARE GOING TO BE WORKED ARE INPUTTED IN TH
PROGRAM THE CELLS WILL BE HIGHLIGHTED ON THE SHEET SHOWING THE HOUR
THAT WILL BE WORKED.
ON COLUMN A I'D HAVE STAFF NAMES;
COLUMN B I'D HAVE HOURS TO BE WORKED;
THEN FROM COLUMN D ONWARDS I'D HAVE EVERY HALF HOUR FROM 9AM TO 8P
(THESE ARE THE CELLS THAT WOULD NEED HIGHLIGHTING ACCORDING TO TH
HOURS THAT WILL BE WORKED)

HOPE YOU CAN HELP
THANK YO

 

[Dave]

HIYA PEOPLE

I AM TRYING TO CREATE A ROTA TIME SHEET FOR WORK. I WANT TO CREATE IT
SO THAT WHEN THE HOURS THAT ARE GOING TO BE WORKED ARE INPUTTED IN THE
PROGRAM THE CELLS WILL BE HIGHLIGHTED ON THE SHEET SHOWING THE HOURS
THAT WILL BE WORKED.
ON COLUMN A I'D HAVE STAFF NAMES;
COLUMN B I'D HAVE HOURS TO BE WORKED;
THEN FROM COLUMN D ONWARDS I'D HAVE EVERY HALF HOUR FROM 9AM TO 8PM
(THESE ARE THE CELLS THAT WOULD NEED HIGHLIGHTING ACCORDING TO THE
HOURS THAT WILL BE WORKED)

HOPE YOU CAN HELP
THANK YOU

If I understand you correctly, this works with one minor change
Col A - Names
Col B - Start time
Col C - Stop time
D1-AA1 - Times every 1/2 hour (09:00, 09:30, ..., 20:30)

Starting in D2, the formula =IF(D$1>=$B2,IF(D$1<=$C2,1,0),0)

Copy the formula from D2 through the lower right hand corner of your
array. So if you have 100 name the lower right corner will be AA101.
Since the formula uses a mix of relative and absolute addressing it will
copy down and adjust without difficulty.

Next set conditional formatting for the range.
Highlight all cells for the desired range first.
Condition 1 will be "Cell Value Is equal to 1"
Condition 1 format will have text color and pattern color matching (i.e.
blue text on a blue background)
Condition 2 will be "Cell Value Is equal to 0"
Condition 2 format will have text color and pattern color matching (i.e.
white text on a white background).

After the formula is entered into the cells and conditional formatting
is set you can input names and times. Times should be entered into
columns B and C in the format 00:00.

HTH
Dave

 

[smitz]

CHEERS DAVE YOU'RE A STAR.
I DID THE FORMULA BUT FORGOT ABOUT CONDITIONS AND THAT. THANKS AGAIN :
: