HANDLING 5-DIGIT HOURS

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
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...
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
hi - welcome to the board!

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

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330

ADVERTISEMENT

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

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330

ADVERTISEMENT

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

New Member
Joined
Dec 20, 2005
Messages
24
(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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Jul 24, 2004
Messages
914
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,251
Members
412,709
Latest member
Rishu
Top