Excel 2013: Subtracting dates

**garywood84** · Sep 29th, 2014, 04:52 PM

I am subtracting dates in Excel 2013. This works fine, except where the result should be 00:00 (hours:minutes), which appears as -00:00. How can I make this appear just as 00:00?

**NeonRedSharpie** · Sep 29th, 2014, 04:54 PM

Can you post two sample dates that are creating this error?

**barry houdini** · Sep 29th, 2014, 04:58 PM

Does it work if you round to the nearest minute, i.e. with this formula

=MROUND(A2-B2,"0:01")

**garywood84** · Sep 29th, 2014, 05:07 PM

Thanks for the very quick replies. I've discovered that it's not subtracting the dates that's the issue, but the fact that I'm embedding this inside another formula. The complete formula I'm trying to use is:

=IF(A9<=TODAY(),B9-C9,"")

Where A9 is a date, and B9 and C9 are two times. I only want the difference between the two times to display if the the date in A9 is today or a date in the past. This works, except if the difference between the two times is 0 minutes, the formula returns -00:00. If I just do B9-C9, Excel correctly displays 00:00.

**garywood84** · Sep 29th, 2014, 05:25 PM

Originally Posted by barry houdini

Does it work if you round to the nearest minute, i.e. with this formula

=MROUND(A2-B2,"0:01")

I can't get this to work at all...

**Rick Rothstein** · Sep 29th, 2014, 05:32 PM

Maybe this?

=IF(A9<=TODAY(),ABS(B9-C9),"")

Note: This formula makes all date differences positive.

**barry houdini** · Sep 29th, 2014, 05:35 PM

My suggestion within your formula will look like this

=IF(A9<=TODAY(),MROUND(B9-C9,"0:01"),"")

**garywood84** · Sep 29th, 2014, 05:35 PM

Originally Posted by Rick Rothstein

Maybe this?

=IF(A9<=TODAY(),ABS(B9-C9),"")

Note: This formula makes all date differences positive.

Thanks, Rick. I'm afraid this won't work, though, because sometimes the result should be negative (it's just that 0:00 should (can!) never be negative!