• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Difference in Timestamps

barmacost

New Member
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

New Member
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

Active Member
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)
 
Top