Date Format Conversion?

C

Chris in Nebraska

I have a tab delimited text file with a column of dates. I copied and
pasted a selection of them below as a reference, there are in reality
over 35,000 rows of these dates.

These text dates must be converted to the following date format and
saved back into a text file as such:

mm/dd/yyyy

That includes leading zeroes in months and dates. E.g. 01/01/1999

To complicate matters, I need to explain the process to others.

Does anyone even know where I should begin?

THANK YOU,
- Chris

+++++++++++++++++++++++++++


9031989
8131990
9201989
1271990
10291989
1081990
3041990
10051989
4191990
10171989
4241990
12181989
11061989
8291989
11021989
12291989
10181989
5151990
11221989
9111989
9111989
5231990
11211989
7271989
10301989
5241990
6081990
9211989
10021990
 
D

Dave Peterson

I would use a helper column with a formula like:

=RIGHT("0"&A1,8)

Then drag this down all 35000 rows.

Then select that column and do data|text to columns
delimited (by nothing)
and choose mdy as the format

Then you could delete the original column or just paste over it.

===
This looks like it will work since your dates are formatted as mddyyyy or
mmddyyyy.
 
C

Chris in Nebraska

Dave -

The helper column solves my first issue - the lack of leading zeros in
months.

I don't understand the "Text to columns" piece yet. I follow the steps
and nothing happens. What SHOULD happen when I do that?

Thanks a *MILLION* for your assistance, by the way!

- Chris
 
E

Earl Kiosterud

Chris,

Excel doesn't see these as bona fide dates. Thus, you can't change the date
format. In a helper column, you could use:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

copied down with the fill handle. This will give bona fide dates, so the
date format can bechanged with Format - Cells - Number - Date. Or use
Format - Number - Date - Custom, and roll your own format codes, mm/dd/yyyy
for your requirement.

Once this column is working, to write it to a text file, you could move
(Cut/Paste) the helper column to the first column of another sheet. Now
save that sheet as a text file.

If you'll be doing this regularly, you might want to use Data - Get external
data, rather than File - Open. That will allow you to keep reading your
text file into an existing workbook already set up with this stuff. That's
importing, rather than opening, a text file. More at
http://www.smokeylake.com/excel/textfiles.htm.
 
C

Chris in Nebraska

***** Earl - MANY THANKS! *****

I believe the formula you have here solves my issue. I don't need (or
actually WANT) the cells to have an Excel date format really, it is
being saved out to text again anyway.

You saved the day!!

- chris
 
D

Dave Peterson

What should have happened is that excel would have converted those text dates
(09302001) into a real date.

You may want to try it again.
 
R

Ron Rosenfeld

I have a tab delimited text file with a column of dates. I copied and
pasted a selection of them below as a reference, there are in reality
over 35,000 rows of these dates.

These text dates must be converted to the following date format and
saved back into a text file as such:

mm/dd/yyyy

That includes leading zeroes in months and dates. E.g. 01/01/1999

To complicate matters, I need to explain the process to others.

Does anyone even know where I should begin?

THANK YOU,
- Chris

+++++++++++++++++++++++++++


9031989
8131990
9201989
1271990
10291989
1081990
3041990
10051989
4191990
10171989
4241990
12181989
11061989
8291989
11021989
12291989
10181989
5151990
11221989
9111989
9111989
5231990
11211989
7271989
10301989
5241990
6081990
9211989
10021990
Click to expand...

If your data is in A1:A35000

B1: =TEXT(A1,"00\/00\/0000")

Fill down to B35000

Then select column B
Edit/Copy
Edit/Paste Special/Values

Delete Column A

--ron
 
D

Dave Peterson

And this works very nicely since the OP wanted to save the data back to a text
file.

But just some thoughts...

If the OP wanted a real date, then a minor modification to Ron's formula may
work:

=--TEXT(A1,"00\/00\/0000")
Format as a date

But this will fail if the windows regional settings for the short date format is
different than the order of the digits in that cell. (Bad sentence!)

If the numbers are mmddyyyy and the regional setting is set for mm/dd/yyyy, then
it works fine.

But if the regional settings for the short date is dd/mm/yyyy, then this will
fail.

(Just a warning that doesn't apply in this case <bg>)
 
E

Earl Kiosterud

Chris,

The formula converts it to Excel date format. Having it in date format
allows you to change the date formatting (Format - Cells ...), which you
said you want. When the sheet is saved to a txt file, it should be exactly
as date-formatted.
 
A

aaron.kempf

excel is a disease because it really doesn't understand datatypes.

i would reccomend uninstalling excel from every machine in the world
and starting over with Access or Crystal Reports.

that way; you can build a report ONCE and run it with parameters;
instead of having to copy and paste 100 different copies of the same
data lol
 
H

Harlan Grove

Dave Peterson wrote...
....
But this will fail if the windows regional settings for the short date format is
different than the order of the digits in that cell. (Bad sentence!)

If the numbers are mmddyyyy and the regional setting is set for mm/dd/yyyy, then
it works fine.

But if the regional settings for the short date is dd/mm/yyyy, then this will
fail.
Click to expand...
....

But your suggestion of using Data > Text to columns would fail if dates
were in ddmmyyyy format and you parsed them as mdy.

Moral: there's no universally correct way to parse dates formatted as 8
decimal digits in sequence. There are different answers for mmddyyy,
ddmmyyyy and yyyymmdd. You could get fancy and adapt your formula based
on trying to convert a random sample using each format, but that seems
overkill.
 
H

Harlan Grove

(e-mail address removed) wrote...
excel is a disease because it really doesn't understand datatypes.

i would reccomend uninstalling excel from every machine in the world
and starting over with Access or Crystal Reports.

that way; you can build a report ONCE and run it with parameters;
instead of having to copy and paste 100 different copies of the same
data lol
Click to expand...

You just won't be able to calculate much. Too bad if that's what you
really need to do.

Also typical that you either didn't read or didn't understand the OP.
The issue is import and parsing, not storage. Data type conversion is
the issue, and Access is no walk on the beach converting 8 sequential
decimal digits into date fields when importing tables from text files.
 
D

Dave Peterson

But in this case, the OP did want mdy. If the data were in some other order,
then the user could chose that order from the dropdown for the date option.

I guess I have a little expectation that the user will be able to choose the
correct sequence to match their data <bg>.
 
A

aaron.kempf

yeah

if you store it in a database; you can RENDER the data in multiple
formats; instead of copying and pasting the same data a dozen times

i mean-- grow up kids; come on

learn ACCESS and then SQL Server.

you won't regret it.

ACCESS is just a much better development environment than Excel.

For starters; it is easy to use all of these formulas in Access-- much
easier than Excel
www.freevbcode.com

ACCESS is better at working with DATA-- ACCESS is better at STORING
data

ACCESS is better at allowing you to FIND ANSWERS instead of digging
through hundreds of worksheets

if you've got a crapload of data in an access database
you've got a beautiful living, breathing database that can be USED by
multiple people and sliced and diced in millions of different ways.

if you've got a crapload of data in excel; you'll have 100 copies of
the sames DATA and you'll run around copying and pasting shit until
your nuts fall off
 
A

aaron.kempf

what.. in Access dont i just use CDATE

because listen-- there are DATA TYPES IN ACCESS AND EXCEL DOESNT HAVE
STRONG ENOUGH VALIDATION TO DO JACK SHIT.

You can have 'DATE FIELDS' in a database.

In excel, you can't really even enforce that a certain cell is a date,
can you?

i mean-- cut and paste gets around protection and validation right?

i just think that it's hilarious-- you kids can keep on making $12/hour
and answering phone calls at lunch

LEARNING DATABASES DOESN'T MAKE YOU A DORK.

Don't be afraid of them; take a couple of classes; read a couple of
books. As it is you guys spend half your day MAKING THE SAME DAMN
SPREADSHEET WEEK IN AND WEEK OUT

Do you really think that is sustainable?

My mission in life is to put spreadsheet dorks out of a job. You guys
are swimming in a cesspool from 1994

-Aaron
 
H

Harlan Grove

(e-mail address removed) wrote...
what.. in Access dont i just use CDATE
Click to expand...
....

Dunno. Do you?

I'll give Access this: when I open a CSV file, it brings up its parsing
wizard. If only Excel would.
In excel, you can't really even enforce that a certain cell is a date,
can you?
Click to expand...

You can, but it requires programming. Ideally, add the cells to a range
defined with a particular name, then use Change and Calculate event
handlers to watch the cells' contents. Data Validation is easily
compromised.

That said, the ability of different cells to hold entries of different
type is one of Excel's strengths. Can it be misused? Sure. But it
allows for flexibility databases lack.
LEARNING DATABASES DOESN'T MAKE YOU A DORK.
Click to expand...

No. As you prove, it comes naturally to some.
My mission in life is to put spreadsheet dorks out of a job. . . .
Click to expand...

So you want to be as unsuccessful as you are pathetic?
 
A

aaron.kempf

technically-- even if you program event handlers to say 'make sure
column F is a date' you can still disable macros and go in and freely
type in whatever you want

im not unsuccessful.

I have offers coming out of my ears for $60-$70/hour. That is MY
rate-- that is what I take home.

Doing little cheesy Access stuff.

I mean jesus-- you spreadsheet dorks really need to wake up to the 21st
century.
 
H

Harlan Grove

(e-mail address removed) wrote...
technically-- even if you program event handlers to say 'make sure
column F is a date' you can still disable macros and go in and freely
type in whatever you want
Click to expand...

You can. You can discourage users from trying to use workbooks with
macros disabled by using do-nothing udf in key formulas. If macros are
disabled, so are udfs, in which case udfs return #NAME? errors which
propagate through downstream formulas.
I mean jesus-- you spreadsheet dorks really need to wake up to the 21st
century.
Click to expand...

You still don't get the main difference between you and most
spreadsheet users. You're paid specifically for database development.
Most spreadsheet users are paid to do things like financial analysis or
marketing studies, and spreadsheets are just one of many tools used.
Databases may be the be-all and end-all of your job(s), but
spreadsheets aren't the central focus of most spreadsheet users' jobs.

When are you going to wake up and realize there are people with jobs
outside IT departments who nevertheless use computers in their jobs?
 
A

aaron.kempf

UDFs run on SQL Server. I do shit like that on a DATABASE SERVER not
on the client.

I am paid to build reports.

you guys dont actually DO jack shit you just drink your foo-foo drinks
and wear your turtlenecks and dont get jack shit done

spreadsheets ARE the central focus of most spreadsheet dorks' job.. i
mean YOU SPEND ALL WEEK BUILDING THE SAME GODDAMN REPORT WEEK IN AND
WEEK OUT

go out and learn crystal or somethign and STFU

and just for the record; i almost NEVER work in IT departments. I work
for 'real world wimps' like you that spend 10 hours a week building the
same goddamn spreadsheets. I mean-- it's fucking disgusting that you
get a single penny for a paycheck.

Financial Analysis? Marketing Studies?

it's all numbers buddy. I mean seriously; do you think that you caress
the numbers better than I do?

oh, nice numbers... nice numbers

grow up.. you are a 'SPREADSHEET DEVELOPER'-- you might as well be
developing in something THAT MAKES SENSE.

from a logistical standpoint-- YOU BUILD THE SAME GODDAMN REPORT WEEK
IN AND WEEK OUT

DO YOU REALLY THINK THAT CRYSTAL REPORTS WON'T REPLACE YOUR JOB?

get a real job spreadsheet kids
 
A

aaron.kempf

and just for the record.. flexibility isn't a good thing when you have
to copy the same goddamn formula to 20,000 different cells.

flexibility isn't a good thing when you have 10 copies of the same
data; all sorted in different ways.

all it causes is BLOAT.


oh, im sorry.. let me wait 10 minutes for this attachment to download.
And then let me forward it to johnnie; save it to a network drive..
make a couple of backup copies

I MEAN-- COME ON KEEP YOUR DATA IN A DATABASE AND SHOVE EXCEL UP YOUR
ASS
 
Top