Tim.
ClioSport Club Member
How do I convert from "1st Jan 2015" to 01/01/15?
ClioSport.net
Register a free account today to become a member!
Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!
-
When you purchase through links on our site, we may earn an affiliate commission. Read more here.
Excel help
- Thread starter Tim.
- Start date
Chrisgti6
ClioSport Club Member
MR2,TT V6,Swift,Mini
Other than re-typing it i'm not sure you will as the original isn't in a date/time format. If you've got to do a lot i'd use a vlookup against a table of dates. 2 columns of data like the below and use the vlookup to correct your originals.
1st Jan 2015 01/01/2015
2nd Jan 2015 02/01/2015
3rd Jan 2015 03/01/2015
4th Jan 2015 04/01/2015
5th Jan 2015 05/01/2015
6th Jan 2015 06/01/2015
1st Jan 2015 01/01/2015
2nd Jan 2015 02/01/2015
3rd Jan 2015 03/01/2015
4th Jan 2015 04/01/2015
5th Jan 2015 05/01/2015
6th Jan 2015 06/01/2015
Tim.
ClioSport Club Member
I've found a solution, where AJ2 has "1st Jan 2015"
=TEXT(LEFT(AJ2,1)&" "&TEXT(DATEVALUE(1&MID(AJ2,5,3)),"mmmm")&" "&RIGHT(AJ2,4),"DD/MM/YYYY")
Result = 01/01/2015
=TEXT(LEFT(AJ2,1)&" "&TEXT(DATEVALUE(1&MID(AJ2,5,3)),"mmmm")&" "&RIGHT(AJ2,4),"DD/MM/YYYY")
Result = 01/01/2015
