formulas for date and times

[lcollier]

Please can someone help......

I would like to add a formula that can tell me the difference in days and time from two cells. An example of the two rows of cells are below

<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=248 border=0><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 86pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=115 height=20>9/1/08 9:20</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 100pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=133>9/3/08 11:55</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>9/1/08 9:20</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">9/1/08 9:31</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>9/1/08 9:24</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">9/1/08 9:49</TD></TR></TBODY></TABLE>
can anyone help?

 

[GlennUK]

What's the largest possible difference? Will it always be below 31 days?

 

[Datsmart]

Take a look at DATEDIF.
This link explains:
http://www.mrexcel.com/forum/showthread.php?t=294065

Opps, this won't help with TIME.

 

[lcollier]

Hi Glenn

This could vary, 1 day or 3 years

 

[GlennUK]

How do you want the results then? Days in one cell, and time in another? Or do you want a string with the full result, as in "xx days hh:mm"?

 

[lcollier]

I would like it in ddhhmm (for example: 2 days 3 hours and 4 mins) but I also need it in working days (so it doesn't include weekends)....hope this makes sense

 

[GlennUK]

I would like it in ddhhmm (for example: 2 days 3 hours and 4 mins) but I also need it in working days (so it doesn't include weekends)....hope this makes sense

I'd guess that you'd want it to go up to ddddhhmm for those differences that are up around the 3 three years mark.

If you do:

=INT(B1-A1)*10000+HOUR(B1-A1)*100+MINUTE(B1-A1)

and format it as 000000, it'll look like you want, or maybe 00000000.

To get working days between 2 dates, make sure that you have the Analysis Toolpak installed and use the NETWORKDAYS function.

 

[barry houdini]

I would like it in ddhhmm (for example: 2 days 3 hours and 4 mins) but I also need it in working days (so it doesn't include weekends)....hope this makes sense

You'd have to define your working day, e.g. if that's 9 to 5 then try putting these times into H2 and H3, i.e. H2 = 09:00 and H3 = 17:00 then, assuming that you have start time and date in A2 and end time and date in B2, and that those "timestamps" will always be within working hours, you can use this formula for working hours

=(NETWORKDAYS(A2,B2)-1)*(H$3-H$2)+MOD(B2,1)-MOD(A2,1)

format as [h]:mm

So for an exact week you'd get a result of 40:00. If you want that to be "5 days 0:00" then change formula to

=NETWORKDAYS(A2,B2)-1-(MOD(B2,1)< MOD(A2,1))&" days "&TEXT(ROUND(MOD(B2-A2,H$3-H$2),5),"h:mm")<MOD(A2,1))&AMP;" p days ?&TEXT(ROUND(MOD(B2-A2,H$3-H$2),9),?h:mm?)<>