Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change the format (2000/xp)

    Does anyone know how to change the following: CALL DATE
    (YYYYMMDD)
    20051130 to 30/11/2005
    (see screenshot for more details):

    Many thanks,
    Skender

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Change the format (2000/xp)

    Insert a new column and use the formula =DATE(LEFT(A3,4),MID(A3,5,2),right(A3,2)). You can then copy & paste values and delete the original column if you wish.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change the format (2000/xp)

    Insert new columns for the date and time. In row 3, enter this formula to retrieve the date:

    =DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2))

    and in the other column, this formula to retrieve the time:

    =TIME(LEFT(B3, 2),MID(B3,3,2),MID(B3,5,2))

    Fill down the formulas as far as needed.

  4. #4
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change the format (2000/xp)

    i have entered the formula but it is not working...probably i have entered it in the wrong place,

    Regards,
    Skender

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change the format (2000/xp)

    Since you haven't told us where you have entered it, it's impossible to comment. Could you post a small sample workbook with the formulas you have tried?

  6. #6
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change the format (2000/xp)

    i am attaching a sample of the workbook, maybe you could try the formula..

    Many thanks,
    skender

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change the format (2000/xp)

    So you didn't even try? <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  8. #8
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change the format (2000/xp)

    I did try it but i have sent you a different file.

    Many thanks again
    skender

  9. #9
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change the format (2000/xp)

    how about the cost, ho wcan i change the cost, the first record 9 is 0.09pence and would like to take other columns off i.e. CALL DATE
    (YYYYMMDD)
    20051130
    20051128
    20051202
    Is there a way to do it apart from hiding columns?

    Cheers
    Skender

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change the format (2000/xp)

    The formula for cost would be similar to the one for duration.

    If you don't need the original columns any more, you could select the cells with formulas, copy them, then Edit | Paste Special..., Values option. This will convert the formulas to values. You can then delete the original columns.

  11. #11
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change the format (2000/xp)

    Is there an easier way to do this, i have got 24212 records on my spreadsheet

    Thanks,
    Skender

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change the format (2000/xp)

    You only need to enter the formulas in the first data row. You can then fill down until the last used row, so it's not much work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •