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 Monopoly should update its pieces?

Discuss in The Lounge

Poll History