Solved Excel - Need To Calculate Days And Hours Between Two Dates

December 19, 2014 at 11:35:25
Specs: Windows 7
CORRECTION TO ORIGINAL CLOSED THREAD

Excel - Need To Calculate Days And Hours Between Two Dates

ORIGINAL THREAD http://www.computing.net/answers/of...

I have tried several of these formulas but I am still having an issue. Here's my problem.

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

=NETWORKDAYS(A2,C2)-1&" Days " &HOUR(MOD(C2-A2,1))&" Hour "&MINUTE(MOD(C2-A2,1))&" Minutes"

The valued return in 1 Days 22 Hour 0 Minutes, which is wrong - it is simply 22 hours. I believe NETWORKDAYS is a count function and if I were only looking for days between it would be close enough, but because I'm looking for the hours between also it seems to be wrong.


Building off of DerbyDad03's formula this is the solution I came up with:

=IF(NETWORKDAYS(A2,C2)-2=-1,(NETWORKDAYS(A2,C2)-1&" Days "&HOUR(MOD(C2-A2,1))&" Hour "&MINUTE(MOD(C2-A2,1))&" Minutes"),(NETWORKDAYS(A2,C2)-2&" Days "&HOUR(MOD(C2-A2,1))&" Hour "&MINUTE(MOD(C2-A2,1))&" Minutes"))


And what I am pretty much saying is if NETWORKDAYS -2 days reutuns -1 day then I want to use the NETWORKDAYS - 1 day formula, if it does not return -1 then I want to do the NETWORKDAYS -2 formula.


See More: Excel - Need To Calculate Days And Hours Between Two Dates

Report •


#1
December 20, 2014 at 17:20:54
✔ Best Answer
I'm currently on vacation.
Will get back to you after the holidays, sorry.

MIKE

http://www.skeptic.com/


Report •

#2
December 22, 2014 at 14:21:57
After rereading your question, not sure what your looking for,
but 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.

In NETWORKDAYS() the calculation is inclusive of both the Start_date and the End_date

So in your example:

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

NETWORKDAYS() will return 2

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

Does that answer your question??

MIKE

http://www.skeptic.com/


Report •

#3
December 23, 2014 at 11:11:36

Report •

Related Solutions

#4
December 26, 2014 at 12:27:14
I was posting an alternative solution that would only return the number of days, hours and minutes between a set of two time stamps. I was not looking for an answer - just bringing to the attention that the original post included what I see is an error.

If you are looking for the total days, hours, minutes in between why would you want to include the start date as 1 day if a full 24 hours did not pass?


Report •


Ask Question