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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
The Text Import Wizard didn't work.

... what were the exact steps you took, and how did you open the file ( what method )?

The formula converts the ddmmyyyy figure in cell A4 into a true Excel date. Change the cell reference as required and put the formula in another column in your spreadsheet and copy down as required.
 
Upvote 0
Click on Excel Menu/Folder, select folder renamed txt file is stored in, dbl clk, Wizard appears, Next, Next, Column data format/Date, select YMD, Finish.
sample that appears in Excel:
7041989 10446 10475 10431 10446 1
10041989 10406 10412 10403 10406 1
11041989 10373 10390 10364 10373 1

" Change the cell reference as required and put the formula in another column in your spreadsheet and copy down as required."
sorry, this doesn't make any sense to me, I need very simple instructions please explaining what to do. There's 4574 lines of data/dates.
 
Upvote 0
Those dates are DMY not YMD. When I chose that at Step 3 I got:

7041989
10/04/1989
11/04/1989

The first one isn't converted because Excel expects 2 digits (leading zeroes) for both day and month.
 
Upvote 0
Why are you selecting YMD when the data is DMY?

As for formulae ... what kind of instructions would you like me to provide exactly?
 
Upvote 0
" Why are you selecting YMD when the data is DMY? "
GlennUK, you wrote: ". . . you can set the date values format expected in the Text Import Wizard. "
I thought by that you were saying that if I used YMD, is would change the date from ddmmyyyyy to yyyymmdd.

" As for formulae ... what kind of instructions would you like me to provide exactly ? "

I do not know how to use Excel.
When you state " Change the cell reference as required and put the formula in another column in your spreadsheet and copy down as required." you might just as well be speaking/writing double Dutch to me. I need as I pointed out simple instructions, simpler obviously than you've provided.
What do you mean by 'Change the cell reference as required' ?
What column do I put the formula in ?
'Copy down as required' does that mean I have to copy the formula 4573 times — once for each date ?

Will the formula convert the date from ddmmyyyy to yyyymmdd ?
 
Upvote 0
" Why are you selecting YMD when the data is DMY? "
GlennUK, you wrote: ". . . you can set the date values format expected in the Text Import Wizard. "
I thought by that you were saying that if I used YMD, is would change the date from ddmmyyyyy to yyyymmdd.

... yes I wrote "set the date values format expected", NOT "set the date values format required". I didn't expect that to be confusing to you.

As for formulae instructions, I don't know what level of expertise you have. That makes a huge difference as to what I tell you to do. See if you can get the Text Import method to work, although Andrew Poulsom has pointed out that your data does not look very consistent. If not, then I'll give you step-by-step instructions on how to enter a formula and copy it down a sheet.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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