Ask Experts Questions for FREE Help !
Ask
    Gecko2954's Avatar
    Gecko2954 Posts: 2, Reputation: 1
    New Member
     
    #1

    Oct 12, 2010, 01:21 AM
    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?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Oct 12, 2010, 03:42 AM

    Are the cells formatted as dates or text?
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    Oct 12, 2010, 11:49 AM

    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.
    Gecko2954's Avatar
    Gecko2954 Posts: 2, Reputation: 1
    New Member
     
    #4

    Oct 13, 2010, 12:25 AM

    The Date is a number sequence without separation characters e.g.. 20051025 = 25 Oct 2005
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #5

    Oct 13, 2010, 03:41 AM

    In that case use the STR() function to convert to a string and then use the Mid function to parse out the components.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #6

    Oct 13, 2010, 07:08 AM

    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.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to connect cells in excel in sequence [ 1 Answers ]

How do you link cells from one sheet to another in a workbook if the cells are in a sequence? I use the formula ='Sheet1'!C2, etc. but entering the formula for each cell is tedious. Is there a faster way if the cells are in sequence?

Transpose rows into columns in a excel spreadsheet [ 3 Answers ]

Can anyone help 1) how to transpose the data in rows into columns and columns into rows in a Excel spread sheet? 2) how to change the numerical order of data in rows/ columns in a Excel spread sheet? i.e.. from the first row to last row and last row to first row ? Thank "U'

Number Sequence [ 5 Answers ]

Here is a number sequence. 33A44B55C3 What does A=, B= & C=?

Number Sequence [ 2 Answers ]

What are the first 3 numbers _ _ _ 10111402188310122124000 I found it once on the net but can't find it again.


View more questions Search