Finding the number of days and time between 2 dates/times in excel

As an example I have 2 columns, A and B - both respectively titled Date and Time.

In A2 I have: 4/03/2012

In A3 I have: 8/03/2012

In B2 I have: 20:34:09

And finally in B3 I have: 17:35:40

So basically I'd like to find out the time between "4/03/2012 20:34:09" and "8/03/2012 17:35:40".

Currently I have: =(A3+B3)-(A2+B2) in the cell A5.

This works as it shows "3/01/1900 21:01" which is the day and time difference between the two.

Also in cell A6 I have a formula which represents this in a nice format: =TEXT(A5, "dd hh:mm:ss"). So the above result looks like "03 21:01:31".

However the problem occurs when A3 is changed to "4/03/2012". Since now A6 shows "#VALUE!" when it should show: 00 02:58:29 . Also A5 shows #####.....etc

I am not quite sure on how to solve this problem. Any suggestions would be appreciated!

The problem you have is that when you change the date the formula is producing a negative number which cannot be displayed. You could try using something like

Please Login or Register  to view this content.

in Cell A5 which reverses the sum to stay positive but would also suggest conditional formating the cell to indicate when time is in a minus state.

You should use Datedif(Startdate,Enddate,format)

I did not think Datedif could handle minus numbers ?