Need a function that calculates time between 2 dates

[RawisTheGameHhH]

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

 

[MartinW]

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

 

[RagDyeR]

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

 

[Bob Phillips]

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"

 

[RawisTheGameHhH]

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

 

[MartinW]

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

HTH
Martin

 

[RawisTheGameHhH]

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?

 

[Bob Phillips]

Only upto 31 days

--
HTH

Bob Phillips

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

 

[daddylonglegs]

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