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.

I think there is a way around the time subtraction issue I have in Excel. The problem is that when the time in cell F (for example 11:29am) is less than the one in cell A (for example 11:30am), the final time subtraction is displaying 59 minutes instead of -1 min(s). The -1 minute is indicating that the whole task was completed 1 minute ahead of the projected time. I am aware that Excel cannot display negative times; instead of Excel displaying negative times in my situation, I would like Excel to still display 1.

Here is the formula I used

=IF(AND(A3="",E3=""),"",IF(OR(A3="",E3=""),"",MOD(E3-A3,1))). 

The formula for the time format conversion from mm: s to mm is

=IF (ISERROR (HOUR (F3)*60+MINUTE (F3)),"", HOUR (F3)*60+MINUTE (F3))

I need improvement to the IF(AND(A3="",E3=""),"",IF(OR(A3="",E3=""),"",MOD(E3-A3,1))) to display a negative/positive time.

Note: A sample copy of the data is attached. The incorrect times are highlighted in YELLOW color.

share|improve this question
1  
If you cross-posted this at another site, and apparently you have, since this site does not feature file attachments, could you please be so kind and post a link to your cross-post the other site, so people here don't waste their time on solving a problem that has already been solved elsewhere? For more information see excelguru.ca/node/7 –  teylyn Apr 22 '13 at 0:59
    
Thanks for the response. Here is the link (ozgrid.com/forum/showthread.php?t=177810) to the other site that has the attachment. Sorry for the cross-post. –  Dappy Apr 22 '13 at 1:09
    
To simplify your formula, you can rip out the IF(AND(A3="",E3=""),"", ... ). If both of the cells are blank, then either of them are, by definition. Since both branches lead to the same result (""), it makes no difference if you just catch blanks in the 'OR' statement. –  mcalex Apr 22 '13 at 13:59
add comment

1 Answer

up vote 1 down vote accepted

Avoid the negative time by taking the absolute value:

=ABS(E3-A3) (assuming start time in A3 and finish time in E3)

You should apply the number format [hh]:mm to the cell so that the result is displayed in hours and minutes rather than the time of day.

In the cell next to the result you can add a description for the time delta:

=IF(E3 < A3,"Ahead",IF(E3 > A3,"Behind","On time")) (same assumptions as earlier)

share|improve this answer
    
Mike, thanks for the response. The initial formula I posted (=IF(AND(A3="",E3=""),"",IF(OR(A3="",E3=""),"",MOD(E3-A3,1)))) is working fine for times subtraction past midnight and during the day. It also works for great when the the larger time (in my case, cell E3) is greater than the lesser time (cell A). What I need is to be able to subtract a lesser time from a larger time (for example, E5(9:29am)-A5(9:30am) = - 1) instead of Excel reading it as the Larger time minus the lesser time. This is what I need to incorporate into the initial formula as it works fine for times past midnight. Thanks –  Dappy Apr 22 '13 at 12:25
    
Here is a sample data to capture the issue I have. Column A Column B 4/22/2013 1:15 PM 4/22/2013 1:30 PM 4/22/2013 11:30 AM 4/22/2013 11:29 AM 4/22/2013 11:30 PM 4/23/2013 12:017 AM 4/23/2013 12:30 AM 4/22/2013 11:59 PM Column C = mod (B1-A1, 1) = 15 minutes Column C = mod (B2-A2, 1) = -1 minute instead of 23:59 Column C = mod (B3-A3, 1) = 47 minutes Column C = mod (B4-A4, 1) = -31 instead of 23:29 I need an excel formula to be able to display -1 or -31 for the scenarios above instead of what the mod function is currently displaying. Thank you. –  Dappy Apr 22 '13 at 23:09
    
@Dappy, in that case try =(B2-A2)*1440 etc and make sure the results are formatted as numbers, not as time/dates. Don't use the MOD() function because that effectively strips the date info, leaving only the time info, which prevents you from doing cross-date arithmetic. –  Mike Fitzpatrick Apr 22 '13 at 23:45
    
Mike, you solved my problem with "Don't use the MOD() function because that effectively strips the date info, leaving only the time info, which prevents you from doing cross-date arithmetic." Your suggestion did the trick. ~5 start answer.~ Thank you. –  Dappy Apr 23 '13 at 3:33
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.