Results 1 to 9 of 9
Thread: Time format (Excel 2000)

20050112, 14:22 #1
 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

20050112, 14:37 #2
 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

20050112, 14:45 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20050112, 14:51 #4
 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

20050112, 14:57 #5
 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

20050112, 14:59 #6
 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

20050112, 15:04 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20050112, 15:09 #8
 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

20050112, 15:24 #9
 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