# Excel 2013: Subtracting dates

#### garywood84

##### New Member
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?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### NeonRedSharpie

##### Well-known Member
Can you post two sample dates that are creating this error?

#### barry houdini

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

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

#### garywood84

##### New Member
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

##### New Member

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

##### MrExcel MVP
Maybe this?

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

Note: This formula makes all date differences positive.

#### barry houdini

##### MrExcel MVP

My suggestion within your formula will look like this

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

#### garywood84

##### New Member
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!

#### garywood84

##### New Member
My suggestion within your formula will look like this

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

Thanks, Barry. I'd tried this, but have tried it again. Excel just returns "0:00" regardless of what the actual difference between B9 and C9 is...

#### barry houdini

##### MrExcel MVP
OK, it seems to work for me, how is the result cell formatted?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,944
Messages
5,856,446
Members
431,814
Latest member
Oriel Tzvi Shaer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back