sheet with dates

F

foxie foxster

hi
i want to make a sheet like this
year: xxxx
month: xxxx
day

after inserting month (eg january) and year (eg 2006)
then the cell beneth "day" must be
1
2
3

.....till (in this case) 31....depending on the month....

then....
if the date is a saterday or a sunday the cell must be colored to eg yellow
and in the cell beside there must be indicated "saterday" or "sunday"

can anyone give some clues?????

thx
 
A

Arvi Laanemets

Hi

Create a sheet Months, with a single-column table Month
Into table, enter 2 sequent starting months (like 01.01.2006 and
01.02.2006), and copy those cells down. Or enter some formula into 1st cell
of table - and copy it down - which calculates 1sts of months you want to
have for use.
Calculate all entries in month table like "yyyy.mmmm" or "yyyy.mm" or "yyyy
mmm" or "mmmm yyyy" - in any valid format.
Define a named range Months, dynamic or static, which includes all entries
in Month table.
(Hide Months sheet.)

On your working sheet:
A1="Month:"
A2="Date:"
B1 - apply data validation list with Source=Months. Format the cell in any
valid date format displaying a date as month - but my advice is to use same
format as you used on Month table.
A3=IF(MONTH($B$1+ROW()-3)=MONTH($B$1),$B$1+ROW()-3,"")
Copy A3 down to range A3:A33. Format as "dd".

There you are! Select month into cell B1, and all days for this month are
displayed. And you have a single cell to determine the month, instead of
two.


Arvi Laanemets
 
Top