Excel newbie question about auto inserting date

A

aznick

Sorry for the trivial question, but I've spent an hour in excel help,
and online trying to find the answer.

I want to make a worksheet where I can type in the month and year in
the upper left corner, and the worksheet fills the top row with the
days (1, 2, 3.....31), and then the row below that the days of the week
(mon, tues, wed.....).

I'm certain this program can do it, but I can't find the answer
anywhere. Thanks.
 
G

Gord Dibben

In A1 type the date

In B1 enter =A1+1 then drag/copy that across to AA1

Format the row as Custom d

In A2 enter =A1 in B2 enter =A2+1 then drag/copy across to AA2

Format the row as Custom ddd

Whenever you change the date in A1, the other values will change also.


Gord Dibben Excel MVP
 
A

Anne Troy

Type 10/2005 in cell A1.
In B1, type =A1
Select A2, Format-->Cells, Numbers tab, choose Custom and type "dd" (no
quotes) into the box. Hit OK.
In C1, type =B1+1
In B2, type =A1
Select B2, Format-->Cells, Numbers tab, choose Custom and type "dddd" (no
quotes) into the box. Hit OK.
In C2, type = C1+1
(you may have to format some of these other cells too)
************
Anne Troy
www.OfficeArticles.com
 
A

aznick

thank you both for your replies. the first method didn't work. I trie
it twice to no avail. The scond worked somewhat, but is it possible t
automate the fact that the months have different numbers of days? Whe
I dragged/copied to AA1 as the instructions indicated, it only numbere
the month to the 27th. If possible, I'd like to put the month and yea
in A1 and have the A row indicate the days (1, 2, 3, etc). and the
row to show mon, tues, wed, etc.

Is it possible to create a formula that allows the table t
automatically adjust the number of days in the month
 
A

Arvi Laanemets

Hi

An example where you can select from 3 last months, the current one included

Create a sheet SetUp
A1="Month"
A2==DATE(YEAR(TODAY()),MONTH(TODAY())+ROW()-4,1)
Copy A2 to A2:A4
Format A2:A4 in some valid date format, you want months to be later
displayed in dropdown list, like Custom "yyyy mmmm"
Define SetUp!$A$2:$A$4 as named range (p.e. Month)
(you can hide the sheet SetUp now - no user intervention is required)


On your main sheet:
A1="Month:"
For cell B1, apply data validation list with source
=Month
Select the month
Format B1 in some valid date format, like Custom "mmmm yyyy" or "mmm", or
....

A3="Day"
A4=IF(MONTH($B$1+ROW()-4)=MONTH($B$1),$B$1+ROW()-4,"")
Format A4 as Custom "dd"
Copy A4 to A4:A35

You have it!
 
R

Roger Govier

Hi

I think Gord meant for you to copy the formulae through to AE1 not AA1 to
cover all of the days through to the 31st.

Clearly, the number of days will vary from 28 to 31, an you will wish to
hide the 1,2 or 3 that would appear at the end of the row in some months.
There are many ways to achieve this but one way would be to use Conditional
Formatting.
Highlight cells AC1:AE2
Format>Conditional Formating>select Formula Is from drop down and in the
white pane type =MONTH(AC1)>MONTH($A$1)
Select Format and set Font colour to be White.


Regards

Roger Govier
 
Top