
1. Re: Calculate time between shifts
Tracy Rodgers May 28, 2013 1:05 PM (in response to Emily Mueller)1 of 1 people found this helpfulHi Emily,
If I understand right, you want to find the difference between the startdtm times. To do this a calculated field similar to the following could be created:
if abs(datediff('minute', max([startdtm]), lookup(max([startdtm]), 1)))>=60 then (abs(datediff('minute', max([startdtm]), lookup(max([startdtm]), 1))))/60
else abs(datediff('minute', max([startdtm]), lookup(max([startdtm]), 1)))
end
Hope this helps!
Tracy

2. Re: Calculate time between shifts
Emily Mueller May 28, 2013 1:31 PM (in response to Tracy Rodgers)Thanks Tracy, but actually what I want to do is calculate the difference between the most recent startdtm, and the last enddtm. So I want to know how many hours it was from when they last punched out, to the next time they punched in. Does that make sense?

3. Re: Calculate time between shifts
Joe Mako May 28, 2013 8:19 PM (in response to Emily Mueller)What if you change the formula for "Time off"
from
DATEDIFF('hour',max([enddtm]),min([startdtm]))
to
DATEDIFF('hour',Lookup(max([enddtm]),1),min([startdtm]))
as in the attached?
Be aware that this uses a table calculations, and requires awareness of your pill types, notice that I changed "Day" to a Measure pill (an ATTR() pill, an aggregation), and set "eventdate" as the compute using.
Using table calculations can get complex fast, especially when working with date data types, there are many expectations and gotchas. You are welcome to contact me if you would like to discuss details.

4. Re: Re: Calculate time between shifts
Emily Mueller Jun 3, 2013 8:35 AM (in response to Joe Mako)Joe, this is great! Thank you!! Maybe you can help me with the next step of this. Now that I can determine how long it has been since their last shift, I have set up a calculated field to say "Reset" if their time off was more than 34 hours. Is it possible to start summing their hours worked since a reset, or since they had 34 or more hours off? We employ truck drivers and we need to follow strict rules about how many hours they can work without a "Reset". So I want to show on this calendar what their cumulative hours are since a reset and until the next reset. Does that make sense?
I have attached a packaged workbook that shows how I am using the Reset.

Packaged 70 hour rule.twbx.zip 593.1 KB


5. Re: Re: Re: Calculate time between shifts
Joe Mako Jun 11, 2013 4:41 PM (in response to Emily Mueller)How about a formula like:
If [Time off]>34 then SUM([Shift Length]) else PREVIOUS_VALUE(0)+SUM([Shift Length]) end
(with the commute using set the same as the other table calcs)

6. Re: Re: Re: Calculate time between shifts
Emily Mueller Jun 12, 2013 7:25 AM (in response to Joe Mako)Joe, I can not thank you enough! Once again I am amazed at what you have come up with!
Thanks again!
Emily