Excel Time Subtraction

0

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.

Dappy

Posted 2013-04-21T23:52:57.227

Reputation: 51

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 http://www.excelguru.ca/node/7

– teylyn – 2013-04-22T00:59:31.920

Thanks for the response. Here is the link (http://www.ozgrid.com/forum/showthread.php?t=177810) to the other site that has the attachment. Sorry for the cross-post.

– Dappy – 2013-04-22T01:09:01.287

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 – 2013-04-22T13:59:55.397

Answers

1

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)

Mike Fitzpatrick

Posted 2013-04-21T23:52:57.227

Reputation: 15 062

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 – 2013-04-22T12:25:38.173

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 – 2013-04-22T23:09:33.747

@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 – 2013-04-22T23:45:22.207

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 – 2013-04-23T03:33:31.730

Asked: 2013-04-21T23:52:57.227

Viewed: 2 819 times

Active: 2013-04-22T03:46:54.717