Convetting dates in excel

[griz]

Comma-Delimited Past Performances (single file format).

give race dates for PP lines like this:
Race Date
20090907
20090801
20090719
20090703

Is the a way to automatically convert them to a more normal
format such as
09/07/2009
08/01/2009
07/17/2009
04/03/2009

The way Bris writes the dates excel can't subtract and give the number of days between dates

[raybo]

If you're not using a parser to change the csv file into a text file then you could write a formula to convert it to another format like:

=CONCATENATE(MID(A1,5,2),"/",MID(A1,7,2),"/",MID(A1,1,4))

Where the original date is in cell A1. The formula is telling Excel to group everything within the first and last parenthesis. The internal functions, "MID(A1,5,2)" look at cell A! and, starting with the 5th character, it returns 2 characters, the 5th and 6th characters, then the ,"/", adds the slash between the month and day, then the next "MID" adds the 7th and 8th characters to get the day, then another slash is added and finally the 1st, 2nd, 3rd, and 4th characters are added to get the year.

[raybo]

You must format the resulting "concatenation" as a date with the format "03/14/98" or similar. Then you can subtract it from another date(see image below)

[raybo]

If you're using a parser, like Infotran, to convert to a text file, when you open the text file in Excel a text import wizard will pop up allowing you to designate which columns are dates and what their format is. Then when it opens in Excel the dates will be in the format "9/07/2009". Then you can subtract dates.

In the image below the highlighted columns were designated as dates, in the text import wizard, the other date columns were not designated so they remained in the yyyy/mm/dd format.

[raybo]

In case you're wondering, the dates in rows 5 and 20 are workout dates (12 workouts available). The dates in column "M" (cells M6 through M15 and M21 through M30) are dates for the last 10 races the horse ran. Thought I'd throw that out there in case you were wondering how I have my Infotran control file set up. It's set up for 15 rows of data per horse which allows me to put the different categories of data on separate rows, and also, to list the last 10 races on separate rows.

[Pcon04]

Raybo

what's the formula to go the other way
12/31/09 to 20091231

thanks
pcon04

[raybo]

Raybo

what's the formula to go the other way
12/31/09 to 20091231

thanks
pcon04

=CONCATENATE(YEAR(A1),MONTH(A1),DAY(A1))

Where the original date is in cell "A1". You can't use the "mid" function to parse a cell that's already formatted as a date, you have to use date functions.

[raybo]

There may be other ways of doing this stuff guys, but, having been self-taught, for the most part, in the use of Excel, I had to figure out how to get Excel to do what I wanted it to do, and that was completely horse racing oriented. I never took a course or ever used Excel in a business environment, so, I don't know all the business stuff that most Excel users were weaned on. I started with Excel with one purpose, to get the race data into it and display it the way I wanted to see it.

So, if someone has easier ways of doing this stuff please jump in, I love to learn new stuff in Excel.

[griz]

I copied your formula and it works perfect. Sorry it took so long to post a thank you. Now I can have days since last raced again.

[raybo]

I copied your formula and it works perfect. Sorry it took so long to post a thank you. Now I can have days since last raced again.

No problem, griz, glad to be able to help a fellow Excel user.