Difference in Timestamps

[barmacost]

I have a column of date/timestamps with each row representing a new entry. I need to determine the difference between row 3 and row 2, row 4 and row 3, row 5 and row 4, etc.


Row 2 2/10/10 9:33

Row 3 2/10/10 10:06

Row 4 2/16/10 18:37

Row 5 2/17/10 8:56

Row 6 2/23/10 16:39

Row 7 2/25/10 17:53


The catch is I need to exclude weekends from the calculation and I don't want the results rounded to the nearest day...I need to account for partial days as well.


It doesn't seem that NetworkDays would be the right formula since it rounds to the nearest # of days. Do I need to build a separate table of just weekend days and exclude them with an index function or is there a formula I'm just not thinking of?


Thanks

 

[TessaES]

I would use networkdays to get the days and an extra formula for the time.

If your dates are in column A you would get:


for the days: networkdays(A1,A2)-1

for the time: if(day(A1)=day(A2),A2-trunc(A2)-(A1-trunc(A1)),A2-trunc(A2)+1-(A1-trunc(A1)))


That is, if you want to count all 24 hours in a day. the trunc() formulas are there to remove the date part.

 

[oldchippy]

Hi,


Yes use the NETWORKDAYS formula for the days, but yoy can use a shorter formula to extract the difference in hours


=MOD(B2-B1,1)

 

[barmacost]

Thank you both! I knew there was an easy way to do this. Thanks again for the lesson.