Xcel 2010 Auto Fill

[BJS]

Can anyone help how I could constuct a format that would allow me to Auto Fill a column like this- Mon-01st, Tues-02nd Wed 03rd and so on. Obviously the day could be different for te first of the month

[soapnumpty]

If you type Mon 1st in the first cell, and Tues 2nd in the one underneath, then highlight both cells. Click and hold down the mouse on the cross in the bottom right of the highlighted area and drag downwards. This should then carry on the format below as Weds 3rd, Thurs 4th etc. hope this works.

[BJS]

Unfortunately this will not work as it just repeats the 2 cells. I use Auto fill a lot but for it to work you have to select/custom the cells with a format first. The trouble I am havig is dd = date mm = month yy = year but can find anything to represent Mon Tues etc and that appears to be the problem.

[ABerrant]

ddd = day (Mon, Tue, etc.)

[soapnumpty]

Sorry, I wasn't much help then. If you don't come up with a better solution you could use the method I suggested but have the days of the week in one column and the date in another. This seems to work.

[pinkyboyid]

The following instructions should give you what you require:

1. First you must create a Custom Format and then apply it to all the Cells that will display the date format you require:

- Highlight the desired Column where the date format is to be displayed, right-click the highlighted column and select 'Format Cells'
- In the 'Category' window select 'Custom'
- in the 'Type' window bar enter
ddd-dd
then select 'OK'
- The custom format is now applied to all the selected column cells

2. Next you need to place a start date somewhere on the spreadsheet, suggest you place it the first cell of the Column you just formatted e.g. 11/2/13 or 11/02/2013, either will do. This should then display as Mon-11.

3. Now for the exciting part!
- In the next Cell directly below the date you just entered type in this formula:
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1

(Note that this formula references the Cell directly above and then increments that value by 1)

4. Now as if by Magic, you should see the next Day Name and Date number sequence as Tue-12

5. To finish, all you need to do now is copy the Cell that you just put the formula into and then Paste it into the Cells that follow-on down the column!

Another option available to go with the above, instead of placing the initial start Date into the first cell as done at step 2, you could place the date anywhere on the spreadsheet and then use that cell reference. e.g =A2 where A2 has the text 11/2/13

Hope this is what you were looking to do and it works ok for you.

[BJS]

Thank you once again pinkyboyid, it works and will save me lots of wok. However modified your instructions a tad to make it more simple for this simple person. Used you No 1 and 2 but then highlighted both and the rest of the desired range filled in automatically. ie put 01/04/13 in cell 1 then 02/04/13 in cell 2 highlighted both cells got small black cross on bottom right of cell 2 and dragged it down and all April's day with correct date were formatted correctly.Thanhs for all the replies as the ddd for the day was also a great tip.