# Solved Calculating the difference between 2 date/times

February 23, 2015 at 06:30:32
Specs: Windows 7
 I have tried using the below formula to workout the difference between 2 dates and times.=NETWORKDAYS(J50,N50,'[Bank Holidays.xlsx]Sheet1'!\$B\$2:\$B\$9)-1&" Days "&HOUR(MOD(N50-J50,1))&" Hour "&MINUTE(MOD(N50-J50,1))&" Minutes"However I have noticed that this doesn't work when the end time is earlier than the start time i.e.Date 1 22/01/2015 14:53Date 223/01/2015 10:58Outcome1 Days 20 Hour 5 MinutesWhat it should be0 Days 20 Hour 5 minutesAny ideas?

See More: Calculating the difference between 2 date/times

February 23, 2015 at 08:19:42
 Unfortunately, NETWORKDAYS() knows nothing about Time, it works only with Days.It's the Time part of the formula that is causing you the grief.Here is a different approach to the problem, see if it helps:http://chandoo.org/wp/2010/09/10/wo...MIKEhttp://www.skeptic.com/

#1
February 23, 2015 at 06:41:58
 In NETWORKDAYS() the calculation is inclusive of both the Start_date and the End_dateSo for example:A2 = Date & Time In = 12/3/14 10:32 AMB2 = Date & Time Out = 12/4/14 8:32 AMNETWORKDAYS() will return 2So, if you DO NOT want to count the Start Day or the End Day in your calculations then -1.MIKEhttp://www.skeptic.com/

Report •

#2
February 23, 2015 at 06:57:36
 You'll see in the formula I already have a -1 in there, but it is still a day out.

Report •

#3
February 23, 2015 at 07:09:48
 The number of days between two dates can be subjective.Some count the End Date as a day, some don't, depends on what your looking for.If you DO NOT want to count the Start Day and the End Day in your calculations then -2MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
February 23, 2015 at 07:47:22
 I appreciate what you are saying.I feel I haven't explained the situation enough.I have a spreadsheet with over 300 rows in it. I am trying to find out the exact days, hours and minutes between 2 dates on each of these rows. Subject to whether I put a -1, -2 etc in the formula the answers are correct.I'm looking for a definitive way of calculating the difference without having to change it depending on the date or time.I.e.Example 1Date 1 - 09/01/2015 14:07Date 2 - 12/01/2015 17:12Formula that gives correct answer: 1 Days 3 Hour 5 Minutes=NETWORKDAYS(J2,O2,'[Bank Holidays.xlsx]Sheet1'!\$B\$2:\$B\$9)-1&" Days "&HOUR(MOD(O2-J2,1))&" Hour "&MINUTE(MOD(O2-J2,1))&" Minutes"Example 2Date 1 - 21/01/2015 15:02Date 2 - 22/01/2015 10:51Formula that gives correct answer: 0 Days 19 Hour 49 Minutes=NETWORKDAYS(J8,O8,'[Bank Holidays.xlsx]Sheet1'!\$B\$2:\$B\$9)-2&" Days "&HOUR(MOD(O8-J8,1))&" Hour "&MINUTE(MOD(O8-J8,1))&" Minutes"I have put an extra column in the spreadsheet to determine whether or not the end time is greater than the start time and then I have to alter the formula accordingly to either -1 or -2

Report •

#5
February 23, 2015 at 08:19:42