Getting Decimal value into time value

[Stephen_IV]

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!

Thanks in advance Stephen

formulas for time.xls
	A	B	C	D	E	F	G
1	Oct	Nov	Dec	Jan	Feb	Mar
2	6	13	14	8			41
3	4	6	6				16
4	8	12	6	14			40
5		6	3	5			14
6	6	4	5	6			21
7	10	12		11			33
8			5				5
9	14	18:30		12			44.5
10		12:17	11	26.3			49.6
11							0
12	12.3	11:20		5			28.6
13	8	16		15.3			39.3
14			5	6			11
15		3	3	4			10
Sheet1

 

[pgc01]

Hi Stephen

Just adjust your formula

=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

 

[lenze]

Here's a code approach

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

 

[Stephen_IV]

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.

Thanks in advance Stephen

Book3
	A	B	C	D	E	F	G	H
1	General	Time	Text			Formatted as [h]:mm		Should be
2	4.5	4:30	4:30			9:11		13:30
3
4
5
6
7
8
9
10
11
12
13
14
Sheet2

 

[Andrew Poulsom]

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.

 

[Stephen_IV]

Thanks Andrew. Works great!