- News
- Reviews
- Bikes
- Accessories
- Accessories - misc
- Computer mounts
- Bags
- Bar ends
- Bike bags & cases
- Bottle cages
- Bottles
- Cameras
- Car racks
- Child seats
- Computers
- Glasses
- GPS units
- Helmets
- Lights - front
- Lights - rear
- Lights - sets
- Locks
- Mirrors
- Mudguards
- Racks
- Pumps & CO2 inflators
- Puncture kits
- Reflectives
- Smart watches
- Stands and racks
- Trailers
- Clothing
- Components
- Bar tape & grips
- Bottom brackets
- Brake & gear cables
- Brake & STI levers
- Brake pads & spares
- Brakes
- Cassettes & freewheels
- Chains
- Chainsets & chainrings
- Derailleurs - front
- Derailleurs - rear
- Forks
- Gear levers & shifters
- Groupsets
- Handlebars & extensions
- Headsets
- Hubs
- Inner tubes
- Pedals
- Quick releases & skewers
- Saddles
- Seatposts
- Stems
- Wheels
- Tyres
- Health, fitness and nutrition
- Tools and workshop
- Miscellaneous
- Tubeless valves
- Buyers Guides
- Features
- Forum
- Recommends
- Podcast
forum
HELP
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.
If you're new please join in and if you have questions pop them below and the forum regulars will answer as best we can.
Latest Forum Topics
Latest Comments
- perce 2 hours 44 min ago
Oh god is he still here? Still not found a publisher for his stories?
- chrisonabike 4 hours 17 min ago
You've been quoted in the article as being concerned "about the safety of cyclist and vehicles”....
- S.E. 8 hours 59 min ago
My guess is that they sell mainly on the German market - feel free to correct me. That might explain why they are not "pushing" their products,...
- ktache 9 hours 24 min ago
Now Emily Chappell really liked the Lezyne , I have the HV version, good, but I find the handle uncomfortable in use. I've often wondered about the...
- biking59boomer 8 hours 31 min ago
What about this one!? Taken in Cardiff Civic Centre 1939
- Muddy Ford 9 hours 34 min ago
Charlie Alliston got 18mths and is the reference case (because there are so few cases) for all campaigns by anti-cyclists that 'cyclists are not...
- belugabob 10 hours 36 min ago
My local football practice pitches (all weather, in a fenced off area, are regularly the scene of inconsiderate parking, with vehicles blocking the...
- Spangly Shiny 11 hours 1 min ago
Assos ASOS anyone?
- chrisonabike 11 hours 14 min ago
Replace them all with Bromptons and they'd take up zero space within days or even hours, as they are all relocated elsewhere. To eBay or possibly...
- Andrewbanshee 11 hours 45 min ago
Where I live we are experiencing a lot of housing development on small and large plots of land. These developers don't seem to have problems...
Add new comment
24 comments
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.
No idea, can't get it to work. I'll keep filling them in and work it out later
Cheers. I'll go have a play about
Four cells like
First name/ Surname/ Nationality/ DOB
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?
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?
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
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.
Date format.jpg
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.
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
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...
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
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
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 [at] gmail.com
Stick it up as a googlesheet as advised
I can do a bit Gkam too
Yes I could spare a few hours Sunday night if that helps.
I can help out this weekend.
Why would we want to involve another site in our fantasy game?
Could you ask PCS or cycling fever if they can help? Would they not already have this data?
If we can get the info in date order then could that be populated on a month by month basis by 12 people?
If you put the spreadsheets on google docs multiple people can fill in information on the same document at once- you can still control access.
Cheers TER, Struggling a little, got 2700 birthdays to fill. I've split of 8 files for other to do if they can, which leaves me about 1400 still to find
I will email you tomorrow if I can do it, need to get some software on my new laptop first