HANDLING 5-DIGIT HOURS

[MrRadio915]

I've been using Excel to graph aggregate listening hours for our radio station www.am91.org. The time format that has worked until this month has been hhhh:mm:ss and the graph has worked well... until this month when the number of listening hours went above 10000.

Using 4 digit hours, Excel converted the time to a standard serial date. I doesn't work with 5-digit hours.

I've tried to convert using =hours(c2)+(minutes(c2)/60)+(seconds(c2)/3600) and it worked for the 4 digit hours but not for the 5 digit.

Other than breaking up the cells into individual cells, I'm at a loss...

 

[Brian from Maui]

You've exceeded the time that can be entered,


9999:59:59

 

[PaddyD]

hi - welcome to the board!

could you give us a few examples of your data and the results you're looking for.

 

[MrRadio915]

Supplying data is no problem.

September 4975:57:59
October 5795:48:51
November 10038:02:02

The expected data I need is:

September 4975.97
October 5795.81
November 10038.03

The data will then be graphed by month.

Thanks for the help!

 

[Barrie Davidson]

Why not just graph by the numbers you've got? i.e., 4975.97, 5795.81, and 10038.03.

 

[MrRadio915]

The numbers represent a page of similar data. I certainly can recalculate the data to give decimal representations, but I would like a formula that would do it rapidly for the existing data and quickly for the data I will have in the future.

 

[Barrie Davidson]

If I understand you correctly, you want to convert data that is time to a decimal value. If that's the case, try
=A1*24
where A1 contains the time. Note that the cell containing the formula should be formatted as a number and not General.

Hope this helps you out

 

[MrRadio915]

(A1)*24 works great for the hhhh:mm:ss format, but it barfs #value when it hits the hhhhh:mm:ss cell.

Maybe I'll just have to do the 5 digit cells manually.

Thanks for your help!

 

[barry houdini]

You could use this formula, format as number

=IF(N(C2),C2*24,LEFT(C2,FIND(":",C2)-1)+MID(C2,FIND(":",C2)+1,2)/60+RIGHT(C2,2)/3600)

 

[BJungheim]

Here's a slightly different solution:

=IF(ISTEXT(B3),LEFT(B3,FIND(":",B3)-1)+("0"&RIGHT(B3,LEN(B3)-FIND(":",B3)+1))*24,B3*24)

[Edit:Typo in formula]