98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Unscrambling Backwards Date Format
Unscrambling Backwards Date Format
Resolved · Medium Priority · Version 2003
Amy has attended:
Excel Advanced course
Unscrambling Backwards Date Format
If a date is written back to front, e.g. 18th March 2008 appears 20080318, what formula unscrambles this into the correct way to read it?
RE: Unscrambling Backwards Date Format
Hi Amy
Thank you for your question - an interesting one.
Is there anything separating the year, month and day, e.g. a hyphen - or forward slash / or are the dates entered in as in your query?
thanks
Amanda
RE: Unscrambling Backwards Date Format
Hi Amanda
There is nothing separating the figures. So 26th March 2008 appear:20080326
However, I have seen a formula entered that suddenly converts these figures into the correct order (day, month, year) and also puts the slashes in, although I dare say this might have been done through formatting.
Many thanks
Amy
RE: Unscrambling Backwards Date Format
Hi Amy
I'll look into this a bit further and see if I can come up with a solution for you.
Kind regards
Amanda
RE: Unscrambling Backwards Date Format
Hi Amy
I've had a look into this, it takes a few different functions to convert the date from the format you have it in to the 'normal' format.
I've attached a spreadsheet so you can see how this works.
I would suggest putting the functions in to extract the year, month and day, and hide the columns or rows you put them into so the spreadsheet's a bit tidier.
I hope this helps.
Amanda
Attached files...
RE: Unscrambling Backwards Date Format
Amanda
I have retrieved an even quicker way to do this as shown below:
Sort a backwards date back to correction:
=DATE(LEFT(F2,4),MID(F2,5,2),RIGHT(F2,2))
Thanks very much for looking into this
Amy
RE: Unscrambling Backwards Date Format
Hi Amy
Thanks, that is very handy :)
Kind regards
Amanda
Wed 25 Feb 2009: Automatically marked as resolved.
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:Editing a formula quicklyIf you want to edit a fomrula or text quickly witin a cell instead of the formula bar, you can click either double click in the cell or press the F2 key |