create a calendar

  • Thread starter Thread starter elenapilin
  • Start date Start date
E

elenapilin

I need to create a calendar, kind of "Outlook" - to make a reservation
for a restaurant (to make sure, the time range from, say, 6 to 8 i
assigned for Mr.Jones and his wife) etc.

Please, help!!
 
Hi

One option

have a different sheet for each day (and a different workbook for each month
or three months)
on the daily sheet set the times the resturant is open across the top of the
page, e.g.
if you open at 9:00am and take the last booking at 11:00pm then type in
cell
B1 9:00
C1 9:30
select both B1 & C1 and drag across the page until you get to 11:00pm
(column AB)

then in column A starting at row 2 type
Table 1
and drag down until all your table numbers are listed (say row20)

To make a booking type the name into the appropriate cell for the time &
table.

Now to automate the "blanking out" of booking times we can use conditional
formatting
the first couple of columns need to be done separately then we can fill the
rest.
(in the following example i have assumed that when you make a booking you
"book" the table for 2 hours .. this can be changed to longer or shorter if
required).

select cell B2
choose format / conditional formatting
choose formula is
and in the white box type (press F2 first after clicking in the box it makes
it easier to type here)
=LEN(OFFSET(B2,0,0))>1
click the format button, go to the patterns tab and choose light fill colour
click OK twice
now click on the fill handle for B2 and fill down to the end of the tables
(ie B20)
- to test that this works type a name in say B3 and the fill colour should
change - delete the name it goes away.

choose cell C2
choose format / conditional formatting
choose formula is
and in the white box type
=OR(LEN(OFFSET(C2,0,0))>1,LEN(OFFSET(C2,0,-1))>1)
click the format button, go to the patterns tab and choose light fill colour
click OK twice
now click on the fill handle for C2 and fill down to the end of the tables
(ie C20)

choose cell D2
choose format / conditional formatting
choose formula is
and in the white box type
=OR(LEN(OFFSET(D2,0,0))>1,LEN(OFFSET(D2,0,-1))>1,LEN(OFFSET(D2,0,-2))>1)
click the format button, go to the patterns tab and choose light fill colour
click OK twice
now click on the fill handle for D2 and fill down to the end of the tables
(ie D20)

choose E2 - do everything as above but the formula this time is
=OR(LEN(OFFSET(E2,0,0))>1,LEN(OFFSET(E2,0,-1))>1,LEN(OFFSET(E2,0,-2))>1,LEN(OFFSET(E2,0,-3))>1)

choose F2 - as for the others but the formula is
=OR(LEN(OFFSET(F2,0,0))>1,LEN(OFFSET(F2,0,-1))>1,LEN(OFFSET(F2,0,-2))>1,LEN(OFFSET(F2,0,-3))>1,LEN(OFFSET(F2,0,-4))>1)

now after you have filled this formula down to F20 you can fill it across to
the end of the times (ie AB20).

this sheet can then be duplicated for each day etc.

Hope this helps
Please get back to me if you require further assistance or email me direct
(julied_ng @ hcts dot net dot au) if you'ld like a sample book set up for
November.

Cheers
JulieD
 
Back
Top