Date format

trdr

New Member
Joined
Oct 5, 2006
Messages
9
Trying to change the ddmmyyyy date to yymmdd or yyyymmdd in a stock prices, Date OHLC txt file.

I tried Data/TexttoColumns/etc which didn't change anything.

WHY don't spreadsheet programs provide yyyy in Format/Cells/Number/Date ???

Besides this small matter driving me crazy, any instructions should be very simple please because I'm not often an Excel user . . .

TIA
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
You say that this is in a txt file. Do you import it via the Import Wizard?
 
Upvote 0
GlennUK, sorry, no it's a 'Microsoft Excel Comma Separated Values File'.

Is there a way I can re-enter the date in yyyy format rather than trying to convert/format it, or does the same problem occur — having to use dd or mm ?
 
Upvote 0
What are you aiming to do after it's in yyyy format exactly?
 
Upvote 0
Open the saved txt file in a charting program to display the data as a price chart.

Unfortunately most charting software suffers from the same problem of not converting all the various date formats !!
 
Upvote 0
It sounds like you'd want the ddmmyyyy as proper Excel dates and custom number format them as yyyy. If you rename the CSV as TXT and re-open it, you can set the date values format expected in the Text Import Wizard. Or you can create formulae to convert ddmmyyyy to a proper date:
Code:
=DATEVALUE(FLOOR(A4/1000000,1)&"/"&FLOOR(MOD(A4,1000000)/10000,1)&"/"&MOD(A4,10000))
formatted as yyyy.
 
Upvote 0
The Text Import Wizard didn't work.

" Or you can create formulae to convert ddmmyyyy to a proper date:
Code:

=DATEVALUE(FLOOR(A4/1000000,1)&"/"&FLOOR(MOD(A4,1000000)/10000,1)&"/"&MOD(A4,10000))


formatted as yyyy."

No idea what the above means. would you explain in simple detail please, what do I do with the code, and does the code convert and maintain the dates ?
 
Upvote 0
The Text Import Wizard didn't work.

" Or you can create formulae to convert ddmmyyyy to a proper date:
Code:

=DATEVALUE(FLOOR(A4/1000000,1)&"/"&FLOOR(MOD(A4,1000000)/10000,1)&"/"&MOD(A4,10000))


formatted as yyyy."

No idea what the above means. would you explain in simple detail please, what do I do with the code, and does the code convert and maintain the dates ?
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top