[Gkam84]

In an effort to speed me up a bit, I’m having a hard time fitting in time to get all the riders birthdays.

So I’m calling on all you guys. If I give you 200 names and countries. Can you fill in the excel file with their birthdays and mail it back to me?

If you fancy it, leave a reply here and I will send you a message through the site.

Need about 8 people (unless you want to do more) to get this task finished and speed up getting the 2015 open…

So who’s up for it?

I’ll give full instructions of what to do and where to find the information.

Topic

[Tinternet_tim]

Just checking I’ve understood
Just checking I’ve understood you correctly
You already have their d.o.b’s but they are not in the correct format?

If I have understood your problem correctly the formulas I have shown in the image I attached works.

If you want to send me the XL document via road.cc message I’ll have a look tonight when I get home from work.

[Gkam84]

No idea, can’t get it to
No idea, can’t get it to work. I’ll keep filling them in and work it out later

[Gkam84]

Cheers. I’ll go have a play
Cheers. I’ll go have a play about

[Tinternet_tim]

In that case, just use the
In that case, just use the formulas from column C to G to convert the date format into the format you required.
You can then drag the formula to the bottom of your dataset to transform all the dates into the correct format and save you a day or more work.
Don’t forget to copy >paste special > value into a new cell to the right of your data. You can then delete the columns with the formulas in to tidy the spreadsheet up.

I’m assuming the above makes sense and does what you wanted?

[Gkam84]

Four cells like
First
Four cells like

First name/ Surname/ Nationality/ DOB

[Tinternet_tim]

Keith,
If it’s all in 1 cell
Keith,
If it’s all in 1 cell try using the attached formulas to get it into the format you want. I have used your data in row 1 to make sure it works.
Row 2 is dummy data to test and row 3 are the formulas I have used to transform the data into the required format.
Copy column G and paste special > values into column H so you have just values and not formulas.

If the dates are in their own cell (e.g. column B) and not contained in a cell with the name and country (column A) you can still use the attached logic, but just formulas from column C to G

If the Months are displayed in a format greater than 3 letters then it’ll be a little more complex but is still possible.

[Tinternet_tim]

Is all of that in 1 cell or
Is all of that in 1 cell or in 3 different cells?

for example
Cell A1 = Keith McRae GBR 11th May 1984
Or
Cell A1 = Keith McRae
Cell B1 = GBR
Cell C1 = 11th May 1984

Also, how is a month displayed if it is more than 3 letters? Is September spelt in full or Sep?

[Gkam84]

At the moment I have it like
At the moment I have it like this on most

Keith McRae GBR 11th May 1984

I want it to be

Keith McRae GBR 11/05/84

I have tried removing the (th) and using excel to format it, with no joy

[Tinternet_tim]

Gkam84 wrote:The birthdays

Gkam84 wrote:

The birthdays are all in the wrong format. I am getting there, only about 1000 to day now

If it is purely a case of the date being in the wrong format then you should be able to use Excel to convert it by either:

Format cell, date

or if the date isn’t in a date format you should be able to use an IF() statement or LEFT(), RIGHT() function. Once the equation is worked out you should be able to do it in seconds.

It is also possible to use a VLOOKUP() to match 1 set of data with another.

If you want to give an example of the problem then I’ll have a look to see if I can work something out.

[Gkam84]

The birthdays are all in the
The birthdays are all in the wrong format. I am getting there, only about 1000 to day now

[cherrypicked]

I am pretty sure this is it
I am pretty sure this is it but can’t download it on safari: http://www.cqranking.com/men/asp/gen/downloads.asp
Hope it helps 🙂

[cherrypicked]

http://www.uci.ch/road/rankin
http://www.uci.ch/road/ranking/

I can’t seem to find place to download entire file on that website. That may be due to mobile device limitations. Maybe I got it from a third party…

[cherrypicked]

Sample
Sample data
Rank Prev. Rider Nat. + Birthdate Team (Code) Points
1 1 VALVERDE BELMONTE Alejandro ESP19800425 MOVISTAR TEAM (MOV) 686
2 2 CONTADOR VELASCO Alberto ESP19821206 TINKOFF-SAXO (TCS) 620
3 3 GERRANS Simon AUS19800516 ORICA GREENEDGE (OGE) 478

[cherrypicked]

I’m pretty sure you can use
I’m pretty sure you can use the UCI id for the birthday. Download the file, manipulate the column and you have them all.

The bigger problem is when you try to match these ids to road.cc and pcs. I tried. Becomes a bit of a manual exercise, after matching some by surname etc Some of the ids (not the uci) change too!

Away on holidays at the moment. See if I can find and post the link

[Gkam84]

Not sticking it into google
Not sticking it into google docs, as that would require giving a link out where anyone couple do anything to it.

I’ve sent a couple of you messages. TheDoctor and Stevemarks I can’t because of your settings. So if anyone else wants to get involved. Fire me an email.

Guitaristkam@gmail.com

[Author]

Replies