Difference between two Excel Date/Time Stamps

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

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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 <[email protected]>
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.

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 <[email protected]>
> 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,

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

Larry

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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 <[email protected]>
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"

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 <[email protected]>
> 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"

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 <[email protected]>
> 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"

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 <[email protected]>
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"

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

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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 <[email protected]>
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"

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 <[email protected]>
> 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"

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 <[email protected]>
> 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"

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 <[email protected]>
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"

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

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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 <[email protected]>
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"

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 <[email protected]>
> 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"

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

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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 <[email protected]>
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"

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 <[email protected]>
> 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"

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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 <[email protected]>
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"

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 <[email protected]>
> 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"

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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

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 <[email protected]>
> 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"

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 <[email protected]>
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"

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

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 <[email protected]>
> 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"

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 <[email protected]>
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"

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

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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 <[email protected]>
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"

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 <[email protected]>
> 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"

One way:

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

In article <[email protected]>,
lnapier <[email protected]> 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 <[email protected]>
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"

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 <[email protected]>
> 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"