Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Excel - transpose date number sequence (https://www.askmehelpdesk.com/showthread.php?t=515861)

  • Oct 12, 2010, 01:21 AM
    Gecko2954
    Excel - transpose date number sequence
    I have a number sequence that is back to front & want to represent this in the standard European format e.g..
    2009-08-24 should be 24/08/2009
    There are several thousand date records to change

    How do I do this?
  • Oct 12, 2010, 03:42 AM
    ScottGem

    Are the cells formatted as dates or text?
  • Oct 12, 2010, 11:49 AM
    JBeaucaire

    If the cells are already formatted as date, you can simply highlight the cells and press CTRL-1 to open the cell formatting wizard and apply a Date format of your choosing.

    If the cells are text strings, you can convert them to date strings with a simple trick:

    1) Highlight the date cells in a single column
    2) Select Data > Text To Columns > Delimited > Next > Next
    3) On the 3rd screen of the wizard, click on DATE: YMD in the upper right corner
    4) Click OK

    The cells now have standard date strings in them and you format the cells to display any way you wish.
  • Oct 13, 2010, 12:25 AM
    Gecko2954

    The Date is a number sequence without separation characters e.g.. 20051025 = 25 Oct 2005
  • Oct 13, 2010, 03:41 AM
    ScottGem

    In that case use the STR() function to convert to a string and then use the Mid function to parse out the components.
  • Oct 13, 2010, 07:08 AM
    JBeaucaire

    Actually, the trick proposed in post #3 will work on text strings like this, too. Just pick YMD so Excel knows how to evaluate the string.

  • All times are GMT -7. The time now is 12:48 PM.