# convert negative time to decimal

#### scamsel

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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### queuesmef

##### Board Regular
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

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

#### NdNoviceHlp

##### Well-known Member
Hi scamsel. Maybe this UDF. HTH. Dave
Module code...
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

##### New Member

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

#### NdNoviceHlp

##### Well-known Member
Hmmm.... place the code in a module. If you put -8:30 formatted as text in sheet1 cell A1, then place...
Code:
``=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

##### Well-known Member

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

##### Well-known Member
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
EF
1-8:30-8.50
2-22:45-22.75
312:1512.25
Sheet919
Cell Formulas
RangeFormula
F1:F3F1=LEFT(E1,FIND(":",E1)-1)&TEXT(MID(E1,FIND(":",E1)+1,2)/60,".00")

#### scamsel

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

##### Well-known Member
You're welcome, thanks for the feedback.

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

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

Replies
2
Views
142
Replies
3
Views
48
Replies
5
Views
74
Replies
6
Views
508
Replies
1
Views
223

1,147,731
Messages
5,742,852
Members
423,758
Latest member

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

### Which adblocker are you using?

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