Brian, so I'm guessing there must be a reason you can't just do a simple math calculation like:
with the 8.5 being a parameter value for total length of the regular hours worked.
What's are the complicating factors?
The other option is DATEDIFF('minute', [end datetime param], [complete]).
I suspect what you really want is for the parameter only to specify the time of day for shift end and to have a calculated field which generates the actual official end datetime for each day.
Post a sample workbook with a realistic spread of data, structured like your data, if you need a hand.
Hi DataVizDude (great name) & Richard Leeke -
Sorry for the delay in responding. The real world really hurt me there for a while.
Here are a couple of monkey wrenches that will intrude into my process that I didn't make clear in my initial post.
1) I want to track time after a specific timeframe because it is an implicit OT indicator. A lot of the work being tracked involves a lot of travel time that isn't necessarily tracked by the system; i.e., I can't simply add up the total number minutes worked and assign a daily hour total; most guys never accrue 8 hours of actual work on tickets b/c they spent so much time site to site. Of course, my results will be generally ballpark as a result, but the consumers are OK with that.
2) Eventually I'll see the request to view this data for other groups, who have different length of day issues; i.e., some people work M-F 8-5 with an hour for lunch, some groups work M-R 7:00-5:30 with thirty minutes for lunch.
3) My function should also be clever enough to understand that any work on S/S counts as OT regardless of timeframes.
For instances where the arrival time > the after hours timeframe, my math gets a bit easier, but mixing the two types in is difficult. What I couldn't figure out how to do is the situation where a partial day is involved; i.e.,
arrival time: 1:00 pm
work complete date time: 5:30 pm
Should come out to ~ 2 hours OT. [when evaluating groups that get off at 3:30]
Any insight is appreciated.
Also, I think that it would be very useful to have a 'scramble my data' button in desktop that would allow you to keep a workbook with data, but anonymousize your desired text fields. Then you can export to .twbx, and fire up to a forum like this for troubleshooting purposes.
I haven't looked at the sample yet (will do tonight) - but just a quick comment on your monkey wrenches.
If you want the approach to work for other groups with different standard work hours it's probably going to be worth defining the groups and their standard work hours in a table which is joined in to your datasource. Otherwise I can see you ending up with ever more convoluted calculated fields which have to list all the different sets of work hours.