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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

trdr

New Member
Joined
Oct 5, 2006
Messages
9
onlyadrafter, nothing changed unless I'm doing something wrong.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You say that this is in a txt file. Do you import it via the Import Wizard?
 

trdr

New Member
Joined
Oct 5, 2006
Messages
9

ADVERTISEMENT

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 ?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
What are you aiming to do after it's in yyyy format exactly?
 

trdr

New Member
Joined
Oct 5, 2006
Messages
9

ADVERTISEMENT

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 !!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

trdr

New Member
Joined
Oct 5, 2006
Messages
9
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 ?
 

trdr

New Member
Joined
Oct 5, 2006
Messages
9
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 ?
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

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
Top