Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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? –  Gimp Sep 20 '12 at 18:36
    
@Gimp -25hours. –  Chris Sep 24 '12 at 19:57
add comment

3 Answers 3

up vote 2 down vote accepted

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?

share|improve this answer
    
That's exactly what I'm looking for - thanks! –  Chris Sep 21 '12 at 10:18
add comment

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.

share|improve this answer
add comment

(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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.