Convert Decimal Hours into Minutes

**hiddenone** · 03-22-2011, 02:34 PM

I have a spreadsheet that tracks working hours by decimal hours. For example, 8hours15 minutes shows as 8.25 hours. This is tracked through a Calendar year. I then have a cell that calculates how much overtime is accrude or how much time needs to be made up. This also show decimal hours in both positive and negative answers depending on the range of hours worked. How do I convert the decimal hours into minutes to show minutes over or minutes under? I tried the below formula and anytime my answer is negative, it says #VALUE in the cell. My formula looks like this: =TEXT((SUM(E10))/1440, "mm:ss").
E10 is the cell that contains the total decimal hours. Formula there looks like this: =SUM(C5:C214).

**Richard Buttrey** · 03-22-2011, 03:01 PM

Hi,

Try wrapping E10 with an =ABS() function i.e.

Please Login or Register  to view this content.

HTH

**hiddenone** · 03-23-2011, 01:25 PM

Thank you. This partially worked. How do I get my answer to show negatives? Right now it's accurately showing the right amount of hours:minutes but everything shows up as a positive number. If time needs to be made up I'd like it to show as a negative.

So now in E10 I have: =ABS(SUM(C5:C214))

And in E11 I have: =TEXT((SUM(E10))/1440, "mm:ss")

Thanks

**mahju** · 03-25-2011, 11:39 AM

Try this one

Please Login or Register  to view this content.

Regard

**Richard Buttrey** · 03-25-2011, 12:48 PM

Hi,

Can you upload your workbook, or at least the relevant part of it?

Regards

**daddylonglegs** · 03-25-2011, 01:00 PM

If you have 8.25 representing 8 hours 15 minutes then you convert that to 495:00 by dividing by 24 and formatting as [m]:ss so based on mahju's suggestion try

=IF(E10<0,"-","")&TEXT(ABS(E10)/24,"[m]:ss")