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...
mid and date functions.xlsRE: 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
