Getting Decimal value into time value

Stephen_IV

Well-known Member
A while ago I had gotten this formula from MarkW it works great. We get different spreadsheet in from vendors and the problem is that the time values are all formatted wrong. What I would like to get is to get all values out of decimal and into time value. If the formula for the row gives me 28.6 or 39.3 I would like to get that into a time value. Can anyone help me using the same setup below!

formulas for time.xls
ABCDEFG
1OctNovDecJanFebMar
261314841
346616
481261440
563514
6645621
710121133
855
91418:301244.5
1012:171126.349.6
110
1212.311:20528.6
1381615.339.3
145611
1533410
Sheet1

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi Stephen

=SUM(IF(TEXT(A2:D2,"hh:mm:ss")+0=A2:D2,1,1/24)*A2:D2)
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

and format column G as [h]:mm.

Hope this helps
PGC

Here's a code approach
Code:
``````Sub ConvertMe()
Dim cl As Range
For Each cl In Selection
If Application.WorksheetFunction.IsNumber(cl) Then
Application.EnableEvents = False
cl = Evaluate("=dollarfr(" & cl & ",60)")
cl.Replace What:=".", Replacement:=":", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:=".", After:=cl, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End If
Next cl
End Sub``````

NOTE: Analysis ToolPak required.

lenze

It's been about a year since this thread, I ran into another problem. If the cell is formated as text then I get a problem with the formula. If I use the formula with just a1:b1 I get the correct answer but wh n the cell is formatted as text then the formula bottoms out. COuld someone please guide me.

Book3
ABCDEFGH
1GeneralTimeTextFormatted as [h]:mmShould be
24.54:304:309:1113:30
3
4
5
6
7
8
9
10
11
12
13
14
Sheet2

This worked for me:

=SUM(IF(ISNUMBER(A2:C2),IF(TEXT(A2:C2,"hh:mm:ss")+0=A2:C2,1,1/24)*A2:C2,TEXT(A2:C2,"hh:mm:ss")+0))

Don't forget Ctrl+Shift+Enter.

Thanks Andrew. Works great!

Replies
12
Views
519
Replies
1
Views
104
Replies
3
Views
532
Replies
1
Views
662
Replies
13
Views
637

1,203,677
Messages
6,056,688
Members
444,883
Latest member
garyarubin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back