Excel 2013: Subtracting dates

garywood84

New Member
Joined
Jan 31, 2010
Messages
22
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?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Does it work if you round to the nearest minute, i.e. with this formula

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

garywood84

New Member
Joined
Jan 31, 2010
Messages
22
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
Maybe this?

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

Note: This formula makes all date differences positive.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

My suggestion within your formula will look like this

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

garywood84

New Member
Joined
Jan 31, 2010
Messages
22
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
Joined
Jan 31, 2010
Messages
22
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,372
Messages
5,595,787
Members
414,021
Latest member
whyjaydee

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top