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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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
150
Replies
3
Views
57
Replies
5
Views
87
Replies
6
Views
529
Replies
1
Views
248

1,148,370
Messages
5,746,303
Members
424,006
Latest member
Metal_warrior

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