# HANDLING 5-DIGIT HOURS

##### New Member
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...

### Excel Facts

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

#### Brian from Maui

##### MrExcel MVP
You've exceeded the time that can be entered,

9999:59:59

##### MrExcel MVP
hi - welcome to the board!

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

##### New Member
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

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

##### New Member
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

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

##### New Member
(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.

#### barry houdini

##### MrExcel MVP
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
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]

Replies
1
Views
261
Replies
2
Views
52
Replies
1
Views
333
Replies
2
Views
252
Replies
17
Views
371