excel help

[[Death]]

hey all, trying to help out me mum , she is doing staff rotas at work. now i can do a few things in excel (far from knowing much!) . just wondering if there is a quick way.

what i want to do is :

coloum 1 coloum 2

1st monday
2nd tues

etc etc

now i know this will change each month, so is there a way to calculate what days etc corrospond by just putting in what month and year it is. so it automaticly does coloum 1 and 2?

right hope i explained that right, confusing myself!

 

[Antar Bolaeisk]

I think you'll have to use some form of script to be able to do this automatically. Say if you enter in the month and year then have the script generate the calender based on this information.

Edit: Google says this

That's going to make a calender so if you want it to spread the days over a single row it'll have to be modified.

 

[Bill Door]

Column 1
The date (in any format, as long as it is recognised by excel as a date)

Column 2
=day(A1)

Column 3
=CHOOSE(WEEKDAY(A1,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Can't think of an easy way to include the 'st' 'nd' 'rd' suffixes on the numbers (at least not this early on a Monday morning).

 

[Antar Bolaeisk]

Well since it will always be

1st, 2nd, 3rd, (9-0)th

You could isolate out the least significant digit and base it on that.

=A1&LOOKUP(RIGHT(A1,1),{"0","1","2","3","4","5","6","7","8","9";"th","st","nd","rd","th","th","th","th","th","th"})

 

[Antar Bolaeisk]

Is it something like this that you're looking for?

Sorry, I got bored studying for exams.

 

[[Death]]

thats perfect m8! nice 1, dont suppose there is way to skip weekends

 

[Namsnik]

Apply a custom format to A1 of MMMM YYYY
Enter the month and year into this cell eg June2011
Excel will interpret this as the first of the month.

In A2:
=A1+IF(WEEKDAY(A1)=7,2,IF(WEEKDAY(A1)=1,1,0))
In A3 and copied down:
=A2+IF(WEEKDAY(A2)=6,3,1)

 

[Antar Bolaeisk]

thats perfect m8! nice 1, dont suppose there is way to skip weekends

This any good?

I've left all the cells used in the calculations on display so you can see how it works but I advise hiding all those cells and locking anything with a formula in it before handing over for use.

Any more study distractions?