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:53

Date 2
23/01/2015 10:58

Outcome
1 Days 20 Hour 5 Minutes

What it should be
0 Days 20 Hour 5 minutes

Any ideas?


See More: Calculating the difference between 2 date/times

Report •

✔ Best Answer
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...

MIKE

http://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_date

So for example:

A2 = Date & Time In = 12/3/14 10:32 AM
B2 = Date & Time Out = 12/4/14 8:32 AM

NETWORKDAYS() will return 2

So, if you DO NOT want to count the Start Day or the End Day in your calculations then -1.

MIKE

http://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 -2

MIKE

http://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 1
Date 1 - 09/01/2015 14:07
Date 2 - 12/01/2015 17:12

Formula 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 2
Date 1 - 21/01/2015 15:02
Date 2 - 22/01/2015 10:51

Formula 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
✔ Best Answer
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...

MIKE

http://www.skeptic.com/


Report •

#6
February 24, 2015 at 03:00:27
Will do. Thanks very much

Report •

Ask Question