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

=IF((A3+B3)-(A2+B2)<0,(A2+B2)-(A3+B3),(A3+B3)-(A2+B2))

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 ?