excel help

Associate
Joined
17 Aug 2005
Posts
1,756
Location
Gloucester, UK
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!
 
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.
 
Last edited:
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).
 
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"})
 
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)
 
[Death];16575360 said:
thats perfect m8! nice 1, dont suppose there is way to skip weekends :P

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?
 
Back
Top Bottom