Excel - convert days hh:mm:ss to minutes (including negative values)

Given data in the following format (i.e. days hours:minutes:seconds):

-4 1:0:0.0

How can I convert this to minutes in Excel? (e.g. 5820 minutes)

I currently do text to columns (days column; hh:mm:ss column) and do =([@Days]+[@Hours])*24*60; but this gives the wrong value where the days are negative (e.g. -5700).

Has anyone got an elegant formula?

-
Can you explain a bit more about how you WANT to handle the negatives? For example should your original time of -1 day 1 hour be translated to -25 hours, or 23 hours? – danielpiestrak Sep 20 '12 at 18:36
@Gimp -25hours. – Chris Sep 24 '12 at 19:57

Assuming there will always be hours in the data (even if zero) and therefore a space try this formula to extract the minutes from your original data in A1

`=(LEFT(A1,FIND(" ",A1))+MID(A1,FIND(" ",A1)+1,20)*IF(LEFT(A1)="-",-1,1))*1440`

That should give -5820 for your example - is that the expected result or do you always want a positive value?

-
That's exactly what I'm looking for - thanks! – Chris Sep 21 '12 at 10:18

Wrap the abs function around the cell reference to the days. For example, if the day was stored in A1, replace the A1 with `abs(A1)`. This will automatically return the number without a sign, so negative numbers will be returned as a positive number, making the function work properly.

I would do this for you, but I don't know how your spreadsheet is setup and what cell format you are using. If you attached (the relevant part) of your spreadsheet to your question (upload it to Dropbox or Mediafire or similar) then I would do it.

-

(I don't understand how Chris is using the @ to indicate column names. Doesn't work for me. In my solution, each column is a named range (Days and Hours), and the bare range name appears in the formula.)

It's important to note here that both columns contain numbers. The Hours column contains a fraction; since it's formatted as a time, that's interpreted as a fraction of a 24-hour period. That has to be true, or the formula

`````` =(Days+Hours)*24*60
``````

wouldn't do anything useful, even for positive values.

Now, we're adding together to values that are always both positive or both negative. The Days value always has the correct sign, but the Hours value is always non-negative, even when it should be negative. So we need to change the Hours value when Days is negative:

`````` =(Days+IF(Days>=0,Hours,-Hours))*24*60
``````
-
I have a table set up - so "Days" and "Hours" are the column header. – Chris Sep 21 '12 at 10:11
OK, nice to learn something. But did my fix work for you? – Isaac Rabinovitch Sep 21 '12 at 17:17