convert negative time to decimal

scamsel

New Member
Joined
Apr 2, 2007
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 19, 2016
Messages
54
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
Joined
Apr 2, 2007
Messages
33
Office Version
  1. 365
Platform
  1. Windows
thank you q, well the results gives a #NAME? error.....
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,914
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
Joined
Apr 2, 2007
Messages
33
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,914
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
Joined
Nov 9, 2002
Messages
2,914

ADVERTISEMENT

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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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")
 
Solution

scamsel

New Member
Joined
Apr 2, 2007
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top