Finding the time between two different times in excel issue

March 27, 2012 at 23:25:58
Specs: Windows 7
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.


See More: Finding the time between two different times in excel issue

Report •

#1
March 28, 2012 at 05:58:11
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

http://www.skeptic.com/


Report •

#2
March 28, 2012 at 07:16:55
See here for how to do Date & Time Math:

http://www.cpearson.com/excel/DateT...

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question