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


--
lnapier
------------------------------------------------------------------------
lnapier's Profile: http://www.excelforum.com/member.php...o&userid=24729
View this thread: http://www.excelforum.com/showthread...hreadid=382956

One way:

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

In article ,
lnapier wrote:

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

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"


lnapier wrote:
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

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.


In article <mWqwe.1823172$6l.6237@pd7tw2no, LenB
wrote:

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"

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.


--
lnapier
------------------------------------------------------------------------
lnapier's Profile: http://www.excelforum.com/member.php...o&userid=24729
View this thread: http://www.excelforum.com/showthread...hreadid=382956

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

JE McGimpsey wrote:
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.


In article <mWqwe.1823172$6l.6237@pd7tw2no, LenB
wrote:


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"

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


--
Jesline
------------------------------------------------------------------------
Jesline's Profile: http://www.excelforum.com/member.php...o&userid=24777
View this thread: http://www.excelforum.com/showthread...hreadid=382956

Jesline,

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

Larry


--
lnapier
------------------------------------------------------------------------
lnapier's Profile: http://www.excelforum.com/member.php...o&userid=24729
View this thread: http://www.excelforum.com/showthread...hreadid=382956