convert negative time to decimal

[scamsel]

hi, anyone know a simple way, where if i have a cell that can have either a positive or negative number of hours (formatted as custom h:mm), i could convert that to an equivalent decimal form of hours such as:
Get -8:30. need to convert to -8.5 hours.
Get 7:00. need to convert to 7 hours.
etc

thank you!

 

[queuesmef]

I'm not sure how you get negative time, but I would use: =HOUR(ABS(A1))+MINUTE(ABS(A1))/60 in column B (its reading from cell A1). If you don't need the ABS formula, then remove it. Make sure the cell the formula is in is formatted correctly - as a number and NOT time. Let me know if this works

 

[scamsel]

thank you q, well the results gives a #NAME? error.....

 

[NdNoviceHlp]

Hi scamsel. Maybe this UDF. HTH. Dave
Module code...

Public Function ConvTime(ShtName As String, TimeAddress As String) As String
'formula =ConvTime("sheet1",A1)
Dim TempSplit As Variant, TempFraction As Double
TempSplit = Split(Format(TimeAddress, "hh:mm") & ":", ":")
TempFraction = (TempSplit(1) / 60) * 100
ConvTime = TempSplit(0) & "." & TempFraction
End Function

To operate enter =ConvTime("sheet1",A1) formula in cell if data in sheet1 A1

 

[scamsel]

hi novice, thank you but i can't say i am able to understand you ha.

 

[NdNoviceHlp]

Hmmm.... place the code in a module. If you put -8:30 formatted as text in sheet1 cell A1, then place...

=ConvTime("sheet1",A1)

in B1 which will output -8.50 in B1
Dave
edit: If U want to format the data as time then U cannot enter a negative number

 

[NdNoviceHlp]

Whoops... I take that back. U can format the negative number as a custom format ( h:mm) or in a time format if you first format the number as text and then change the format to either the custom format or time format... makes no sense. Dave

 

[jtakw]

Hi,

@NdNoviceHlp , you were right the 1st time, you Can Not have time as a negative. What you said in Post #7 works because the underlying value is Text, even if you re-format the cell as time, and the cell shows something like -8:30 , that is actually Text. If you Click Into cell again (double click) and hit Enter, Excel will not accept it as Time.

So assuming OP's values are Text, may be this will work:
Hopefully, OP values are not Mixed, where some are Text (the negative ones), and some are Real Time values.

Book3.xlsx
	E	F
1	-8:30	-8.50
2	-22:45	-22.75
3	12:15	12.25
Sheet919

Cell Formulas
Range	Formula
F1:F3	F1	=LEFT(E1,FIND(":",E1)-1)&TEXT(MID(E1,FIND(":",E1)+1,2)/60,".00")

 

[scamsel]

hmm, thanks jtak, this works! i just have to multiply the formula by 1, and now its a usable format that i can sum up.

thanks all for the help

 

[jtakw]

You're welcome, thanks for the feedback.

Alternatively, I normally just add 0, either way will work.

=(LEFT(E1,FIND(":",E1)-1)&TEXT(MID(E1,FIND(":",E1)+1,2)/60,".00"))+0