98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Unformated Dates
Unformated Dates
Resolved · Low Priority · Version 2007
Wed 4 Aug 2010, 16:35Delegate Emma said...
Emma has attended:
Excel Intermediate course
Unformated Dates
I have a list of dates that will not change into the correct english format what formulae is the best to sort this out. I know len, right, mid and left formulae are used.
Thu 5 Aug 2010, 07:57Trainer Anthony said...
RE: Unformated Dates
Hi Emma, thanks for your query.
Here's the formula we looked at in class which converts both 90210 and 100210 into date format. I've broken the syntax of the IF statement up to make it more readable here:
=IF(LEN(A1)=6,
(MID(A1, (LEN(A1)-5),2)) & "/" & (MID(A1, (LEN(A1)-3),2))& "/" & RIGHT(A1,2),
(MID(A1, (LEN(A1)-4),1)) & "/" & (MID(A1, (LEN(A1)-3),2))& "/" & RIGHT(A1,2))
I think you should be able to tease this apart, but let me know if you need any further help with this.
All the best,
Anthony
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Highlighting only Text cellsTo select onlt text value cells in a spreadsheet, click on Edit-Go to(F5) |
