Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates & Time (Excel 2000/2002)

    Hallo
    I sit with the problem that I have thousands of rows of data with dates and times in a different format than I need it.
    The problem is that I have two sets of data that I have to get in the correct format.
    I attach a work sheet showing the formats of the un-desired formats and the desired format.
    I would appreciate any ideas how I can accomplish this.

    Regards
    Regards
    Kobus

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

    Re: Dates & Time (Excel 2000/2002)

    For cells that contain date and time combined, such as A4:
    Enter the formula =TRUNC(A4) in the cell where you want the date, and format this as a date.
    Enter the formula =MOD(A4,4) in the cell where you want the time, and format this as a time.

    For cells that contain yyyymmdd, such as C4:
    Enter the formula =DATE(LEFT(C4,4),MID(C4,5,2),MID(C4,7,2)) in the cell where you want the date, and format as a date.
    For cells that contain hhmmss, such as D4:
    Enter the formula =TIME(TRUNC(D4/10000),TRUNC(MOD(D4,10000)/100),MOD(D4,100)) in the cell where you want the time, and format as a time

    You can fill down each of these formulas as far as needed.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates & Time (Excel 2000/2002)

    Hans

    Thank you very much, your formulas is going to save me at least a week or two.
    I really appreciate your help.

    Thank you again
    Regards
    Kobus

Posting Permissions

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