Time Help

[Charles Hoens]

I've written loads of time/date functions, but this one has me stumped.

I have a group of employees working different dates and times, so my table has name, startdate, starttime, enddate, endtime for each time period the employee worked. So, for example, the table might show,

John Jones, 10/11/2014 07:00 10/11/2014 15:00.

From this data I need to determine the number of hours this person worked during 1st, 2nd or 3rd shift. For this project, 1st shift is 7am to 3pm, 2nd shift is 3pm to 11pm and 3rd shift is 11pm to 7am.

Ideally, what I would like is to say for each time line, x 1st shift hours, y 2nd shift hours and z 3rd shift hours.

I was thinking of using a function for this, but I can't wrap my head around all the various possible inputs (i.e. xx:15 or xx:30 or xx:45) Do I run a loop and test for every 15 minutes of time and then store that to an incrementing field? Seems daunting!

Can anyone give me a start to this?

Thanks!

[Peter.Greulich]

Re: Time Help

Charles,

If I am following you all you need is a calc field to subtract the time diff. The way to do this is to use a date-time field, which in dbf Alpha calls a time field type.

You're familiar with date-time values:

? now()
= 10/14/2014 03:25:31 18 pm

You can also create a DT value like this:

d = "12/31/50"
t = "05:03:20"
?ctodt(d + " " + t)
= 12/31/1950 05:03:20 00 am

You can subtract DT values:

d = "10/14/2014"
t = "15:26:20"
t1 = ctodt(d + " " + t)
?t1
= 10/14/2014 03:26:20 00 pm


d = "10/15/2014"
t = "05:03:20"
t2 = ctodt(d + " " + t)
?t2
= 10/15/2014 05:03:20 00 am


? (t2 - t1)/3600
= 13.6166666666667

Check out the date-time functions. Alpha has dozens.

[Charles Hoens]

Re: Time Help

Thanks Peter. I think I understand all this. However my question is: Given a span of time, how do I determine how many hours are attributable to each shift?

That's why I was contemplating a function with a loop built into it. I would give the function the time span and it would return the number of hours in Shift 1, etc.

Perhaps I'll write it out and it will make more sense.

Thanks again.

[GGandhi]

Re: Time Help

I do this for my employee payroll, but I calculate the total hours people worked but this method can be broken into shifts too, I am sure.
I convert the start time to minutes and end time as minutes then subtract to get the minutes and convert back to hours and minutes. you can always do the math for ending shift.
for example some one starts at 7:23 am and end 4:37 pm and the shift is 7-3 3-11 and the third is 11-7
then start shifts are 420, 900 and 2380
time started 443 time ended 997
so the person worked in the 1st shift 900-443=457 minutes, then you divide that and use modulus to get the hour and minutes
so the person worked in the 2nd shift 997 - 900 = 97 minutes, then you divide that and use modulus to get the hour and minutes
for the third shift add 24 to the ending hour for calculations purposes.

edit: second shift added

[Peter.Greulich]

Re: Time Help

However my question is: Given a span of time, how do I determine how many hours are attributable to each shift?

Now I understand (I think). So the 1st shift is 7am to 3pm, but the guy works 8am to 4pm, so he has 7 hrs in shift 1 and 1 hr in shift 2. Take a look at the between function. Using a case or if-else statement you can compare start end times and then calc accordingly.

[Charles Hoens]

Re: Time Help

Yes, Peter that's the idea. I'm going to try to create a function and see if that does the trick and report back, hopefully by morning!

[GGandhi]

Re: Time Help

It's kind of like the Russians when they constructed their first A-Bomb. They had the luxury of knowing it "could" be done!

I guess you are in that position today when you try to create a function.
wish you all the best.

[workaholic06]

Re: Time Help

Charles,

What do you use for your database? Just wondering, as I can see this working in a view if it's MySQL or SQL.