List Dates

[nathanuel81]

Hey there,
I am currently creating a project in excel for my AS Level ICT exam. I
need to create a timetable for appointments. I have a good veiw of how
i would like it to work in my head but i just cannot figure out how to
actually get it working.
I would like for excel to import the dates and days of a month when i
enter the name of the month and the year.

e.g.

2006 January
| 1st SAT | 2nd SUN | ect

Any help would be greatly appreciated.

Thanks Fanle

 

[Biff]

Hi!

Here's one way that gets pretty close:

The returned value will look like this:

1 - Sun (January 1 2006 is on Sunday)

Create this named array:

Insert>Name>Define
Name: Months
Refers to:
={"january";"february";"march";"april";"may";"june";"july";"august";"september";"october";"november";"december"}

A1 = 2006
B1 = January

=IF(OR($A1="",$B1=""),"",IF(COLUMNS($A:A)<=DAY(DATE($A1,MATCH($B1,months,0)+1,0)),DATE($A1,MATCH($B1,months,0),COLUMNS($A:A)),""))

Copy across to 31 cells.

Format the cells as CUSTOM: d - ddd

Biff

 

[nathanuel81]

when i type that in i get a #Value! error

it may have been the way i typed it in though. Could you go through
step by step because im not to sure where to out the 1st array

thanks

 

[Biff]

Hi!

It probably has to do with that named array.

Instead of using a named array you could just list the months in a range of
cells and either name that range or simply refer to that range:

IV1:IV12 = January; February; March; etc

=IF(OR($A1="",$B1=""),"",IF(COLUMNS($A:A)<=DAY(DATE($A1,MATCH($B1,IV1:IV12,0)+1,0)),DATE($A1,MATCH($B1,IV1:IV12,0),COLUMNS($A:A)),""))

Biff

 

[nathanuel81]

Hi!

It probably has to do with that named array.

Instead of using a named array you could just list the months in a range of
cells and either name that range or simply refer to that range:

IV1:IV12 = January; February; March; etc

=IF(OR($A1="",$B1=""),"",IF(COLUMNS($A:A)<=DAY(DATE($A1,MATCH($B1,IV1:IV12,0)+1,0)),DATE($A1,MATCH($B1,IV1:IV12,0),COLUMNS($A:A)),""))

Biff

Right it works for the 1st date but after i try and drag it across for
the other dates i get a #Ref! error. Also if i copy and paste the code
into the next cell i get the same date again.

Thanks

 

[nathanuel81]

Hi!

It probably has to do with that named array.

Instead of using a named array you could just list the months in a range of
cells and either name that range or simply refer to that range:

IV1:IV12 = January; February; March; etc

=IF(OR($A1="",$B1=""),"",IF(COLUMNS($A:A)<=DAY(DATE($A1,MATCH($B1,IV1:IV12,0)+1,0)),DATE($A1,MATCH($B1,IV1:IV12,0),COLUMNS($A:A)),""))

Biff

Right it works for the 1st date but after i try and drag it across for
the other dates i get a #Ref! error. Also if i copy and paste the code
into the next cell i get the same date again.

Thanks

 

[Biff]

Hi!

You must be doing something wrong. Want to see a sample file? Just let me
know where to send it.

Biff

 

[nathanuel81]

lol as usual its me
could you send it to (e-mail address removed)

 

[Biff]

Sent file

Biff