Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time format (Excel 2000)

    Hi gurus,

    I have extracted data from an internal source which gives time in one column. There is a problem with the time format. Normally the time has to be written as 2:00PM or 14:00hrs, however in the present case the extracted data for the same time is shown as 1400 in the coloum. Further the time was stored in the system as UTC (GMT) but i require the local time for my working, which I can get by adding the time zone difference in hours. eg the time in the coloum is shown as 1400 but the actual time is 22:00hrs. Can someone suggest me how to go about solving this problem.
    Thanks in advance
    novice

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time format (Excel 2000)

    This could be any one of several different problems. Instead of wasting a lot of time guessing and asking questions, could you upload a workbook that shows what you have in that column? You can delete all other data in the workbook.
    Legare Coleman

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

    Re: Time format (Excel 2000)

    Excel stores times as fractions of 1 day, e.g. 6:00 AM is stored as 0.25 (6 hours = 6/24 = 1/4 of one day). To convert 1430 to a number that represents a time, you must take the integer part after dividing by 100, i.e. 14, add the time zone difference: 14+8 = 22, calculate mod 24 (you don't want 25 or 37), divide this by the number of hours in a day: 22/24, next take the remainder after dividing by 100, i.e. 30, and divide this by the number of minutes in a day: 30/1440, and add this together.
    Say that the 1400 is in A1. In B1, enter this formula:
    <code>=MOD(INT(A1/100)+8,24)/24+MOD(A1,100)/1440</code>
    and format B1 as a time. Fill down as far as needed.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time format (Excel 2000)

    As requested the excel file is attached.
    Novice

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time format (Excel 2000)

    Great Hans

    My problem is solved.
    Thanks once again.
    Novice

  6. #6
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time format (Excel 2000)

    Hans,

    pls see my attached worksheet. Now how do i change the date when the time goes beyond 24 hours.

    Novice

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

    Re: Time format (Excel 2000)

    See attached worlbook.

    Would have been nice if you had mentioned at the outset that the dates needed to be corrected too.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time format (Excel 2000)

    Thanks Hans,

    Thi is even better. Sorry for ddescribing my problem in bits and pieces.

    Novice

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time format (Excel 2000)

    Put the formula below in cell D2 and copy it down to the other cells in column D. Now, format these cells as a date plus time (both in one cell).

    <pre>=B2+TIME(INT(C2/100),MOD(C2,100),0)+8/24
    </pre>


    If you really need a separate date and time in D and E, then put this formula in D2 and copy it down:

    <pre>=INT(B2+TIME(INT(C2/100),MOD(C2,100),0)+8/24)
    </pre>


    And this formula in E2 and copy it down:

    <pre>=MOD(B2+TIME(INT(C2/100),MOD(C2,100),0)+8/24,24)
    </pre>

    Legare Coleman

Posting Permissions

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