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 2

23/01/2015 10:58Outcome

1 Days 20 Hour 5 MinutesWhat it should be

0 Days 20 Hour 5 minutesAny ideas?

✔ 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

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 AMNETWORKDAYS() will return 2

So, if you DO NOT want to count the Start Day

the End Day in your calculations then -1.orMIKE

You'll see in the formula I already have a -1 in there, but it is still a day out.

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

the End Day in your calculations then -2andMIKE

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

Date 1 - 21/01/2015 15:02

Date 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

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

Will do. Thanks very much

Ask Your Question

Weekly Poll

Do you think SpaceX laying off some of its workforce will help it succeed?

Discuss in The Lounge

Poll History