• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto-fill date horizontally/date split into 2 cells Excel 2003

M

marykate

New Member
Hello,

I am trying to do a weekly calendar listing dates horizontally, but split into 2 cells, i.e.

Sept 21 would be 9 in A1, 21 in A2

Sept 28 would be 9 in B1, 28 in B2

Oct 5 would be 10 in C1, 5 in C2

I am using excel 2003. I enter dates vertically using auto fill. Dates immediately change from numeric month to words, i.e. 9/1 changes to 1-Sep. In order to fit, I need months to be in number form, it won't allow me to change back to number form. When I split into 2 columns, using text to columns, the months shows as 9-Jan or 10-Jan. I can't get rid of the -Jan, and the year shows, which I don't want/can't use. I ultimately would like to get the dates set up correctly, then transpose them.

This is not really a big deal, but if there were a way for me to list dates horizontally, 2 cells for each date, using autofill, my life would be so much easier.

Again, I am using Excel 2003 which may be the problem. I am not very well versed in Excel which is definitely part of the problem!

Any help would be greatly appreciated. If it turns out there's no way to do this, at least I will stop hitting my head against the wall trying!
 
You just need to change the format of the cells.

In A1, input the date of Sept 21. Then, in B1 you can do:

=A1+7

In A2, you can simply put:

=A1

and copy to the right.


Now, to get things looking correct, select row 1, Format cell, Number tab. Select a custom format of:

m

and then for row 2, a custom format of:

d


This tells XL to only show the month/day respectively, as a number.
 
Hi,


Insert 21/09/2012 in A1 custom format cell to "m"

Insert 21/09/2012 in B1 custom format cell to "d"

Insert =A1+7 in cell C1 custom format to "m"

Insert =B1+7 in cell D1 custom format to "d"

Select cells C1 and D1 and autofill across as far as needed
 
Luke M, it worked! I cannot tell you how happy this makes me! I've wasted so much time trying to figure this out, I knew there must be a way, just didn't quite know how to accomplish it. Thank you so much! You've made my job so much easier!!!


oldchippy, thank you also for your help. I didn't need to use it because the first suggestion worked for me.


Thank you both for taking the time to help me out. I really appreciate it. This board is a great resource, especially for someone like me who knows Excel has so much potential, but is now sure how to go about using it.


Thanks again to both of you.
 
marykate,


Very glad we were able to help you out. Feel free to come back and ask questions anytime. =)
 
You must log in or register to reply here.
Back
Top