# Thread: Time format (Excel 2000)

1. ## 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.
novice

2. ## 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.

3. ## 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. ## Re: Time format (Excel 2000)

As requested the excel file is attached.
Novice

5. ## Re: Time format (Excel 2000)

Great Hans

My problem is solved.
Thanks once again.
Novice

6. ## 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. ## 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. ## Re: Time format (Excel 2000)

Thanks Hans,

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

Novice

9. ## 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>

#### Posting Permissions

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