5 Replies Latest reply on Apr 9, 2012 3:37 PM by Richard Leeke

Calculating Number Of Minutes Past Time

Hello friends -

My data looks like this:

arrival date time:  mm/dd/yyyy hh:mm:ss am

work complete date time:  mm/dd/yyyy hh:mm:ss pm

number of minutes worked:  int value

I've been requested to get a look at all 'overtime' work.  Capturing events where work complete date time > a parameter defined hour was easy enough.  But, what I can't figure out how to do is calculate the number of minutes of actual overtime worked.

For example, if someone had a unit of work like this:

arrival: 4/6/12 07:00:00 am

complete: 4/6/12 17:00:00 pm

number of minutes worked: 600

Great.  BUT.  If the users are usually supposed to work 7 - 3:30, I'd really like to be able to show the 'extra' minutes this unit of work accrued; i.e., 90.

Can anyone explain how to get there?

Thanks.

brian

• 1. Re: Calculating Number Of Minutes Past Time

Brian, so I'm guessing there must be a reason you can't just do a simple math calculation like:

600-(8.5*60)

with the 8.5 being a parameter value for total length of the regular hours worked.

What's are the complicating factors?

--Shawn

• 2. Re: Calculating Number Of Minutes Past Time

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.

• 3. Re: Calculating Number Of Minutes Past Time

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.

Thanks!

brian

• 4. Re: Calculating Number Of Minutes Past Time

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.

brian

• 5. Re: Calculating Number Of Minutes Past Time

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.