Elapsed Time between 2 dates

[ronadona1]

I have two dates and I want to know the difference showing Days, Hours and Minutes.
I want to see in one cell 4 days, ? Hours, ?Minutes

<TABLE style="WIDTH: 325pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=432 border=0 x:str><COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><TBODY><TR style="HEIGHT: 32.25pt; mso-height-source: userset" height=43><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 107pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 32.25pt; BACKGROUND-COLOR: white" width=142 height=43 x:num="39609.841666666667">06/10/2008 08:12:00 PM</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=130 x:num="39616.987037037034">06/17/2008 11:41:20 PM</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 120pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=160>7 Days 209 Minutes</TD></TR></TBODY></TABLE>

 

[VoG]

Try

=DATEDIF(A1,B1,"d")&" d "&INT((MOD(B1,1)-MOD(A1,1))*1440)& " min"

 

[ronadona1]

Thanks so much! That works but it shows me days and Minutes and I was wondering is you could modify it to show Days Hours Minutes?

Thanks again!

Here's your original code that give Day and Minutes
=DATEDIF(A1,B1,"d")&" d "&INT((MOD(B1,1)-MOD(A1,1))*1440)& " min"

 

[barry houdini]

Try

=INT(B1-A1)&" days, "&TEXT(B1-A1,"h"" hours, ""m"" minutes""")

....or if your time difference is always less than 32 days you could also try this way. Use just this formula

=B1-A1

and custom format result cell as

d "days, "h" hours, "m "minutes"

 

[barry houdini]

Try

=DATEDIF(A1,B1,"d")&" d "&INT((MOD(B1,1)-MOD(A1,1))*1440)& " min"

You have to be careful with this, If the time element of A1 is greater than the time element of B1 then you get erroneous results, e.g. if A1 is today at 21:30 and B1 is tomorrow at 07:00 then the answer should be

0 d 630 min but your formula returns 1 d -871 min

 

[MarkVMcCullagh]

Try =INT(B1-A1)&" D, "&INT(MOD(B1-A1,1)*24)&" H, "&INT((MOD(B1-A1,1)*24-INT(MOD(B1-A1,1)*24))*60)&" M"

 

[MarkVMcCullagh]

My previous method won't work properly on negatives

Another possibility is

=ABS(B1-A1)

and format the cell something along the lines

d" Days" h:m

 

[ronadona1]

YOU ARE A GENIUS!!! That works PERFECTLY the =ABS formula combined with the custom format is the ANSWER!!! Thank you!!! Thank you!!!!

 

[ronadona1]

OK one more little thing the customer wants for this TAT report. They need to remove weekends and holidays (the Network days function) and display it in the same manner. So if the dates in A1 and B2 string across a weekend or holiday, then instead of counting all the days, only count the work days.

Like here is an actual date from the report. The calculation will show 7 days but it's really 4
<TABLE style="WIDTH: 236pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=314 border=0 x:str><COLGROUP><COL style="WIDTH: 138pt; mso-width-source: userset; mso-width-alt: 6729" width=184><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><TBODY><TR style="HEIGHT: 32.25pt; mso-height-source: userset" height=43><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 138pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 32.25pt; BACKGROUND-COLOR: white" width=184 height=43 x:num="39609.841666666667">06/10/2008 08:12:00 PM</TD><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=130 x:num="39616.987037037034">06/17/2008 11:41:20 PM</TD></TR></TBODY></TABLE>
Thank you so much!!!

 

[barry houdini]

Why is it 4 and not 5 days?

Assuming that B1 is always greater than A1 and that both dates will be on weekdays (i.e. Monday to Friday) then you can use this formula

=(NETWORKDAYS(A1,B1)-1-(MOD(B1,1)< MOD(A1,1)))&" days "&TEXT(B1-A1,"h"" hours ""m"" minutes""")

which is a text result

or this formula

=(NETWORKDAYS(A1,B1)-1-(MOD(B1,1)< MOD(A1,1)))+MOD(B1-A1,1)

formatted like

d "days, "h" hours, "m "minutes"

Note: as stated in my previous post, this second option won't be appropriate if you have long time periods because it wont correctly show any time periods of 32 [working] days or more

NETWORKDAYS function requires Analysis ToolPak add-in to be enabled and also allows you to specify a list of holidays that can also be excluded from your count