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!

Essentially the problem occurs when the end time is less than the start time.

Excel does not allow negative TIME, so if the Start Time is before the End Time you will always get an error.

~~You can always do a check with an IF to see if the Start Time is before the End Time~~

and either reverse you calculations or pop up a message, or simply blank/zero out the answer.

~~Something like:~~

~~=IF(ISERROR((A3+B3)-(A2+B2)),"Error",(A3+B3)-(A2+B2))~~

or

=IF(ISERROR((A3+B3)-(A2+B2)),(A2+B2)-(A3+B3),(A3+B3)-(A2+B2))EDIT: Just did some checking and the above does not work.

Will have to play with this a bit.

MIKE

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History