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.
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.920Thanks 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.287To 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