# 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/2012In A3 I have: 8/03/2012In B2 I have: 20:34:09And finally in B3 I have: 17:35:40So 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 #####.....etcI 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

#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

Report •

#2 March 28, 2012 at 07:16:55
 See here for how to do Date & Time Math:http://www.cpearson.com/excel/DateT...MIKEhttp://www.skeptic.com/

Report •
Related Solutions 