Difference between two Excel Date/Time Stamps

[lnapier]

How do I obtain the days, hours, min and sec between two date/time
stamps represented in an excel spreadsheet?

For example:

I have "4/6/2005 10:00:00 AM" in cell A1 and "4/4/2005 7:00:00 AM" in
cell A2. How can I obtain the dd:hh:mm:ss between both date/time stamps
in cell A3?

A
_________________

1 4/6/2005 10:00:00 AM
2 4/4/2005 7:00:00 AM
3 ??Days, ??Hrs, ??Min, ??Sec

 

[JE McGimpsey]

One way:

=INT(A2-A1) & " Days, " & TEXT(MOD(A2-A1,1), "h"" Hrs, ""m"" Min,
""s"" Sec""")

 

[LenB]

Another way:
in A3, =A1-A2 or maybe =abs(A1-A2) if A2 is not always earlier.
Format A3 as custom with: d"Days," h"Hrs," m"Min," s"Sec"

 

[JE McGimpsey]

Note that "d" is the format code for day of the month, so this will give
the wrong result if the user's date system setting is the 1904 system.

Likewise it will be wrong if the elapsed time is more than 31 days.

 

[lnapier]

Thanks to JE McGimpsey for the below solution, which works great to
determine the duration between the two dates. I was really impressed
with the time it took for you to respond. Thanks a million. However I
have some more questions if you don't mind.

Solution:

=INT(A2-A1) & " Days, " & TEXT(MOD(A2-A1,1), "h"" Hrs, ""m"" Min,
""s"" Sec""")


Here's a new challenge!

My next question is how can I have the calculation cull out weekends
and holidays from the duration that the formula provides? If I have a
duration that spans across a weekend and then a holiday between two
dates, I don't want the duration result to reflect that time.


Additionally, would it be possible to further constrain the result by
culling out off-hours time between (1700-0600) daily? I only want to
count time that has expired between (0600 - 1700) which is our standard
work day.

 

[LenB]

Ooops. My mistake. Thanks for pointing it out.
Len

 

[Jesline]

Hi

If I have a loan that is rollover from 15 Dec 04 till 28 Mar 05. I need
a formula to autoculate the accrued no of days for interest in Jan, Feb
and Mar. ie Jan =31 days, Feb = 28 days and Mar = 28 days.

Is it possible?

thks for helping

 

[lnapier]

Jesline,

I'm still trying to figure out my issue. Please start your ow
discussion thread concerning your inquiry. Thanks.

Larr