Limit to hours in elapsed time format?

[Kaykayme]

Each year I receive a text file or HTML which I have to convert to an Excel
spreadsheet and separate the data by means of Text to Columns. Once this is
done I must calculate the total time of usage for each group (Cost Center).
The problem is the some of the cells have the time in the format
DAYS:HOURS:MINUTES:SECONDS. To solve this problem I have used the LEFT
function to separate the DAYS and multiply this by 24 hours to give the total
hours for days. Then I use the MID function to separate the hours and add
this to the total hours for days. This total is combined with the minutes
and seconds and put in a cell with the numberformat of "[h]:mm:ss". This
worked fine last year. This year I discovered if the hours were over 9999
the numberformat changed to "Text" and could not be calculated. So to solve
this problem I copied the line and subtracted from the total 9999 and use
this as the hours for the first line. The second line has the remainder of
the hours. When I totaled all of the hours there was no problem with the
grand total or even the subtotals having more than 9999 hours. My questions
is why did I have to do this in the first place? Is there a limit to the
amount of hours the elapsed time will show? Any suggestions for a more
efficient code.

 

[venkat1926]

perhaps I have not understood your problem .

SupposeA2 and A3 are

11/7/2009 9:45:13

11/7/2009 9:47:56

A4 is A3-A2
CUSTOM format A4 as dd(space)hh:mm:ss
in the above case it will be
0 0:02:43 that is 0 days 2 minutes and 43 seconds.

will this not serve your problem. If so confirm.

 

[Gary''s Student]

Your problem is trying to convert the value to hours rather than time. Say
we have in A1 the following text:

314:11:34:52

this is 314 days, 11 hours, 34 minutes, and 52 seconds

The number of hours should be 314*24+11 or 21947

We use a formula similar to yours:

=--LEFT(A1,FIND(":",A1,1)-1)+TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(":",A1,1)))

We are getting the value BEFORE the first colon as days and the value AFTER
the first colon as time.

the formula displays:
914.4825463
and format the cell to [hh]:mm:ss and it displays:
21947:34:52

Since 21947 is greater than 9999, your problem should be solved.

 

[Kaykayme]

Thank you for your input. However, this will not solve the problem. First
of all according to my calculation on the spreadsheet 314 days *24hrs = 7536
hrs then + 11 hrs = 7547. This is not over 9999 as you have stated below.
Also I have tried to put in the format [hh]:mm:ss and this did not change the
format from text to time. The format must be [h]:mm:ss to be calculated as
time. This also does not explain why when all the time is calculated into
one grand total, the grand total displays as time and not text.

Your problem is trying to convert the value to hours rather than time. Say
we have in A1 the following text:

314:11:34:52

this is 314 days, 11 hours, 34 minutes, and 52 seconds

The number of hours should be 314*24+11 or 21947

We use a formula similar to yours:

=--LEFT(A1,FIND(":",A1,1)-1)+TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(":",A1,1)))

We are getting the value BEFORE the first colon as days and the value AFTER
the first colon as time.

the formula displays:
914.4825463
and format the cell to [hh]:mm:ss and it displays:
21947:34:52

Since 21947 is greater than 9999, your problem should be solved.

--
Gary''s Student - gsnu200908

Each year I receive a text file or HTML which I have to convert to an Excel
spreadsheet and separate the data by means of Text to Columns. Once this is
done I must calculate the total time of usage for each group (Cost Center).
The problem is the some of the cells have the time in the format
DAYS:HOURS:MINUTES:SECONDS. To solve this problem I have used the LEFT
function to separate the DAYS and multiply this by 24 hours to give the total
hours for days. Then I use the MID function to separate the hours and add
this to the total hours for days. This total is combined with the minutes
and seconds and put in a cell with the numberformat of "[h]:mm:ss". This
worked fine last year. This year I discovered if the hours were over 9999
the numberformat changed to "Text" and could not be calculated. So to solve
this problem I copied the line and subtracted from the total 9999 and use
this as the hours for the first line. The second line has the remainder of
the hours. When I totaled all of the hours there was no problem with the
grand total or even the subtotals having more than 9999 hours. My questions
is why did I have to do this in the first place? Is there a limit to the
amount of hours the elapsed time will show? Any suggestions for a more
efficient code.