extra characters added when splitting dates using 'data text to columns'

[RelEd]

Hello, I am trying to create an weekly attendance sheet with dates listed horizontally. Each date must be split into two cells due to space constraints, i.e. cell 1a is September, cell 1b is 15, cell 2a is September, cell 2b is 22 etc. I used autofill to first create dates, then plan to transpose info after I split the month from the date. This is where the problem starts. In the month, I have numbers added before and after the actual month: 1/then month/1900. I cannot get rid of the first number. When I try to get rid of the 1900 it becomes 2011 which I can't get rid of. I don't have space for this on my attendance sheets. Am I doing something wrong or is there some sort of glitch with my computer? I have Windows XP, Excel 2003. I'm starting to think I should just do this manually, but I'd love to know if there's a way to do it using Excel. Thanks so much for anyone who could help me.

 

[baitmaster]

its your cell formatting. Change results to numbers (ctrl + 1 to open cell format window)

 

[Jonmo1]

I think text to columns is not the right approach..
If your dates are REAL Dates, then they are numbers, not text.
Therefor by definition, "Text to columns" is not the right function to use.
It actually only sees the serial number for the date, It doesn't see "October" or "September"
It does it's best to interprait your formatting of the date, but it's just not quite good enough..

I would recommend a formula approach to extract the Month and Day.

If your Date is in A1

B1: =TEXT(A1,"mmmm")
C1: =DAY(A1)

Then fill down.


Hope that helps.

 

[RelEd]

I totally see the logic in this formula, but I am embarrassed to say I'm not sure how to apply it. I have the date in A1, but not sure how to go about using the formula. I don't use Excel for anything too complicated. Feeling pretty stupid but could you explain a little further? Thank you!

 

[Jonmo1]

Just select B1, and type
=TEXT(A1,"mmmm")
and press enter

Then select C1 and type
=DAY(A1)
and press enter


Now B1 shows the month as September or October or whatever
And C1 shows the Day 20 or 22 or whatever


Then highlight B1 and C1 - Right click and copy
Then highlight B2 and C2 - Right click Paste

That duplicates the formula into the next cells.

You can do multiple rows at the same time too
Highlight B3 through C20 (or however far down you need to go)
Right Click Paste.


Hope that helps.

 

[RelEd]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CKathy%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi, the first part worked great, when I entered the formula the date appeared in the two cells, however when I copied & pasted, the new showed up as January 0 (changed from September 11). I wonder if it's something wrong with my computer?

 

[Jonmo1]

There would also need to be dates in A2, A3, A4 etc...as you copy it down.

 

[RelEd]

Hey! That worked great for the dates! The only problem, when I went to transpose the dates to make them horizontal, instead of the date info I got #REF! in each box. Any idea what that would mean? And thanks so much for your patience!

 

[Jonmo1]

Put the formulas in before you do the transpose.

Then, INCLUDE those 2 columns when you copy/transpose the data.

 

[RelEd]

I did put the formula in, and got the month in one box and date in another, just like I wanted them. When I transposed them, I got #REF! in place of the months and dates.