Crosswords0 min ago
Xcel 2010 Auto Fill
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
Answers
Best Answer
No best answer has yet been selected by BJS. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.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.
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.
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.
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.
Related Questions
Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.