unscrambling backwards date form

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Unscrambling Backwards Date Format

Unscrambling Backwards Date Format

resolvedResolved · 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...

mid and date functions.xls

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.


 

Excel tip:

Editing a formula quickly

If 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

View all Excel hints and tips


Server loaded in 0.08 secs.