Calendar

[John McGibbon]

Hi All,

I'm trying to put together a workbook which will allow me to enter a start
date and finish date (different cells) in Sheet 1. Is there a way of
transfering this to a "Calendar" (essentially a row of numbers)
automatically on Sheet 2 and identify, perhaps by colour?

I would visualise it similar to below:

On Sheet 1
Start Finish
Holiday 01/01/2007 05/01/2007



On Sheet 2
January 2007
S M T W T F S
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31


Thanks in advance...

 

[Dave Peterson]

Maybe you can glean some ideas from Chip Pearson.

Check out the Calendar download from:
http://www.cpearson.com/excel/download.htm

 

[Bernie Deitrick]

John,

On Sheet1, name the cell with the 1/1 StartDate, name the cell with the 5/1 FinshDate, then on the
other sheet, select cell A1, enter this formula

=DATE(YEAR(StartDate),MONTH(StartDate),1)-WEEKDAY(DATE(YEAR(StartDate),MONTH(StartDate),1))+1+(ROW()-ROW($A$2))*7+(COLUMN()-COLUMN($A$2))

and copy to A1:G7.

Then Select A1:G1, format custom for dddd.

Format A2:G7 for date or for d

Then select A2:G7 and use the CF "Formula is" option, with the formula

=AND(A2>=StartDate,A2<=FinishDate)

and select the shading that you want.

IF the start and finish date are not in the same months, then copy A7:G7 down a few rows to extend
the calendar.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Oops, typo:

name the cell with the 5/1 FinshDate

should have been

name the cell with the 5/1 FinishDate

to be consistent with the CF formula.

HTH,
Bernie
MS Excel MVP

 

[John McGibbon]

Thank You.

John

Oops, typo:

name the cell with the 5/1 FinshDate

should have been

name the cell with the 5/1 FinishDate

to be consistent with the CF formula.

HTH,
Bernie
MS Excel MVP