Need a function that calculates time between 2 dates

I have been playing around with a function that will give me the difference between to dates and times.

Example:
A1 contains 6/10/06 10:00AM
A2 contains 6/13/06 10:00PM

I am trying to get a function to give me an answer with how many days, hours and minutes between the 2 dates/times.

the answer would be 3 days, 12 hours, 0 minutes

Hi Rawis,

In A3 put =A2-A1
And format cell as custom d:hh:mm for 3:12:00 or
dd:hh:mm for 03:12:00

HTH
Martin

anyway to make it say days after days, hours after hours,etx

Check out this link:

http://www.cpearson.com/excel/datedif.htm

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"RawisTheGameHhH"
<[email protected]> wrote in
message news:[email protected]...

I have been playing around with a function that will give me the
difference between to dates and times.

Example:
A1 contains 6/10/06 10:00AM
A2 contains 6/13/06 10:00PM

I am trying to get a function to give me an answer with how many days,
hours and minutes between the 2 dates/times.

the answer would be 3 days, 12 hours, 0 minutes


--
RawisTheGameHhH
------------------------------------------------------------------------
RawisTheGameHhH's Profile:
http://www.excelforum.com/member.php...o&userid=35945
View this thread: http://www.excelforum.com/showthread...hreadid=557337

Just subtract one from the other and format as

d "days, " h "hours, " m "mins, " s "secs"

This falls down if more than 31 days are betgween dates though, where you
would need

=INT(A2-1)-INT(A1+1)+(MOD(A2,1)+(1-MOD(A1,1))>=1)&" days "&
TEXT(MOD((MOD(A2,1)+(1-MOD(A1,1))),1),"h ""hours, "" m ""mins, "" s
""secs""")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RawisTheGameHhH"
<[email protected]> wrote in
message news:[email protected]...
>
> I have been playing around with a function that will give me the
> difference between to dates and times.
>
> Example:
> A1 contains 6/10/06 10:00AM
> A2 contains 6/13/06 10:00PM
>
> I am trying to get a function to give me an answer with how many days,
> hours and minutes between the 2 dates/times.
>
> the answer would be 3 days, 12 hours, 0 minutes
>
>
> --
> RawisTheGameHhH
> ------------------------------------------------------------------------
> RawisTheGameHhH's Profile:
http://www.excelforum.com/member.php...o&userid=35945
> View this thread: http://www.excelforum.com/showthread...hreadid=557337
>

Re-reading your post also try custom d"days"hh"hours"mm"mins"

HTH
Martin

Originally Posted by MartinW

Re-reading your post also try custom d"days"hh"hours"mm"mins"

HTH
Martin

you guys are great! anyway to have it so it wont count the weekends?

Only upto 31 days

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MartinW" <[email protected]> wrote in message
news:[email protected]...
> Re-reading your post also try custom d"days"hh"hours"mm"mins"
>
> HTH
> Martin
>
>

Originally Posted by RawisTheGameHhH

anyway to have it so it wont count the weekends?

assuming both A1 and A2 are on weekdays

=(NETWORKDAYS(A1,A2)-1)+MOD(A2,1)-MOD(A1,1)

format as before