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.