Days of the month formula

[Managerwork]

Hi - im trying to create a rota for staffing but im sure there must be a quicker way than manually typing 'sat 1 - sun 2 - mon 3 ' into each cell! I've attached an example of the format that i use and would like to stick with, i just dont know how to make a formula for this. any thoughts? thanks

EDIT :

OK there was a solution provided, here it is -

Put the date into cell D85 (THIS CAN BE ANY CELL NUMBER, D85 IS JUST THE ONE I USED)

then in cell B1 (OR WHERE YOU NEED IT) entered the following formula -

=$D$85 (IF YOU'VE USED A DIFFERENT CELL NUMBER THEN RELATE TO THAT - I USED D85)

then once that's come up with the date i've right clicked and customised the display so that it says 'Thur 1'

Then in the next cell (C1) i've entered the formula - "=B1+1"

[martindwilson]

put the real date eg 1/6/2013 in cell,custom format as ddd d fill across

[Fotis1991]

Hi

1 way could be this.

Type in a cell the first day of the month. Let's say in A3 type 01/07/2013

In A1 use this.

=TEXT(WEEKDAY($A$3),"ddd")&" "&COLUMN(A1)

In B1 and copy across use this one.

=TEXT(WEEKDAY($A$3+COLUMN(A1)),"ddd")&" "&COLUMN(B1)

Does this works for you?

[Managerwork]

Ace! cheers for that Fotis - thanks also Martin.

I have a couple of questions if i may -

Martin: I don't understand how I would custom format the cell? (I don't understand what this means)

Fotis - I've done your method and it works - but i really don't understand the formula! Is there any way I can read about what this formula is or what the parts of it are?

Thanks

[akhileshgs]

Custom format is something we 'teach' to our excel to be the format of our need.
Suppose if you have a date like 1-Jul-12, u can change it to 1-Jul-2012 by using custom format.
Got it?

And coming to your point,
right click on the desired date; in the category you will see the botton option as 'custom',
after selecting that, you will see someting on the 'Type' field below 'Sample'
remove that, and put your own format there.
In this case, copy the below code and paste

ddd d  

it will show your desired result in the sample.
Hope this helps

[Managerwork]

Fotis - I'm having a bit of trouble with that formula actually - It works fine if i use it as you said, however (due to the layout of the rota) I need to change thing's about a bit (A column is used for staff names)

So I tried to set it out as following -

Put the date into a cell that wouldn't conflict with the rota information (I chose cell D85 - any cell over 80 will be fine though)

then I amended the formula's as follows -

In cell B1 i entered the following -

=TEXT(WEEKDAY($D$85),"ddd")&" "&COLUMN(B1)

and this displayed "Thur 2" (Instead of "Thur 1")

However when i came to do the formula in C1 something odd happens - i used the following

In cell C1 :

=TEXT(WEEKDAY($D$85+COLUMN(B1)),"ddd")&" "&COLUMN(C1)



This displays "Sat 3" rather than what it should be "Fri 2"

So i don't really understand why it's skipped a day and its begun on the 2nd instead of the 1st?

Thanks

[Managerwork]

akhileshgs - thanks for the reply. Im not actually seeing the option for custom as you mentioned though?

I've attached a screen shot of what is displayed when i right click on a cell that i have entered the date into.

cheers

[Fotis1991]

Upload a sample workbook with data that looks like your original, pls.

[martindwilson]

right click cell
choose format cells
choose custom
type ddd d
cick ok

[Debraj Roy]

Hi Managerwork!

please update your Profile...
Its says Excel 2003..
Screenshot is MacExcel..

[Managerwork]

Hi Fotis - here's the file i've tested this on. thanks

[Managerwork]

Goof point Debraj - i think I was on a different computer when I filled that in, will do now

[martindwilson]

choose this option

[Fotis1991]

In B1

=TEXT(WEEKDAY($D$85),"ddd")&" "&COLUMN(B1)-1

In C1 and copy across

=TEXT(WEEKDAY($D$85+COLUMN(B1)-1),"ddd")&" "&COLUMN(B1)

[Managerwork]

Hey Martin - that works thanks very much for that

Fotis - The new formula that you've posted works great to thankyou! I have a question though -

I don't really understand why the minus ones are there (obviously they are needed as it now works)

What would i have to read about to learn the methodology behind what you've done? I'd like to understand this.

Are there any advantages to the way you suggested or is it just the way you're used to working?

Thanks a lot all

[martindwilson]

since you are referencing another cell you need to do it slightly differently see attached

[Managerwork]

Not following Martin - the way that you said previously worked?

Is this another way?

[martindwilson]

most things with dates are best done with formatting as the underlying value is always a real date and not just text that enables more flexibility when making calculations.
eg you cant say =if(a1>b1,1,0) where dates are just text like sat 1

[Fotis1991]

You are welcome!

Read here what column function does.

Read here to see what TEXT function does.

Excel has the advantage that offers solutions using different ways. So in this case the answer is that i just followed my own way.. I don't know if is the best way..and to be honest i feel that no one knows..

[Managerwork]

Martin - so you're suggesting using formula's so that should i need to do any calculations I'm able to reference cells better? Sounds like fairly reasonable practice. I can't imagine what I might do with the day's of the month? (I'm open to suggestions though ) I've entered your formula though and It works fine, i'm impressed that the following cells figure out from just "E3+1". Good stuff.

Fotis - Thanks for the link's ill have a look. I recently brought a book (Slaying Excel dragon's) That I'm going to be working through - but this stuff (that you have helped me with) is directly relating to stuff that I'm doing so it's good to read about directly (rather than wait till page 333 or something) I'll check those links... And I've done a lot of music editing so I get that theres never, EVER, one way to do something when it come's to editing / programming things.

[martindwilson]

Not following Martin - the way that you said previously worked?

in your example b1 references cell d85 you cant drag b1 across as it wont auto increment so you have to put =b1+1 in c1 and fill that across instead

[Managerwork]

Cheers Martin - the way that I did it (I'm guessing that this is the way you meant) was :

Put the date into cell D85

then in cell B1 entered the following formula -

=$D$85

then once that's come up with the date i've right clicked and customised the display so that it says 'Thur 1'

Then in the next cell (C1) i've entered the formula - "=B1+1"

And then i can drag out from there.

Is that what you mean?

[martindwilson]

yes exactly that

[Managerwork]

ace cheers martin

[Fotis1991]

Thank you for the rep. At last Martin's suggestion was more simple so more good!

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

[Managerwork]

no worries - done and edited first post (as best as i could!)