1 of 1 people found this helpful
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)))
Hope this helps!
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?
What if you change the formula for "Time off"
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.
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
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)
Joe, I can not thank you enough! Once again I am amazed at what you have come up with!