8 Replies Latest reply on Aug 15, 2013 7:54 PM by Dekade

# Need help on COMPLEX Time calculations

### Title

Need help on COMPLEX Time calculations

### Post

I have a situation where an employee shift starts at 11:00 PM on a Thursday for example. The employee shift ends at 7:30 am Friday (the next day).

Here is the calculation that I use to figure hours worked because of the shift starting on one day and ending on the following day:

Case ( Exclude Lunch = "YES" ;  Round ((( TimestampEnd - TimestampStart)  / 3600)-.5 ; 2 ); Exclude Lunch = "NO" ;  Round (( TimestampEnd - TimestampStart)  / 3600 ; 2 ))

Here is where I need to get more technical

The one hour before Thursday midnight is a different pay scale than the 7-1/2 hours on Friday morning midnight shift.

FIRST Calculation Result - I need to create a calculation that takes the timestamped one hour, or two hours, or three hours (etc.) Before midnight on Thursday - and does a calculation that places the amount of hours into a "Standard Pay Rate HOURS Field".

SECOND Calculation Result - I need to create a calculation that takes the timestamped 7-1/2 hours, or 9 hours, or 10-1/4 hours (etc.) AFTER midnight on Friday - and does a calculation that places those amount of hours into a "Shift Pay Rate Hours Field".

• ###### 1. Re: Need help on COMPLEX Time calculations

Assuming that this calculation takes place at the moment the worker "clocks out" (about the same time as TimestampEnd):

StandardHours:

Let ( Thu = GetAsTimeStamp ( GetasDate ( Get ( CurrentTimeStamp) ) - DayOfWeek ( Get ( CurrentTimeStamp) ) + 5 & " 12:00 am " ) ;
If ( Thu < TimeStampEnd ; Thu - TimeStampStart ; TimeStampEnd - TimeStampStart )
) / 3600

ShiftPayHours:

Let ( Thu = GetAsTimeStamp ( GetasDate ( Get ( CurrentTimeStamp) ) - DayOfWeek ( Get ( CurrentTimeStamp) ) + 5 & " 12:00 am " ) ;
If ( Thu > TimeStampStart ; TimeStampEnd - TimeStampStart ; TimeStampEnd - Thu )
) / 3600

• ###### 2. Re: Need help on COMPLEX Time calculations

Hi PhilModJunk,

Got delayed in checking replies.

I don't really know what you are saying in " Assuming that this calculation takes place at the moment the worker "clocks out" (about the same time as TimestampEnd):" I think you are meaning that the calculation(s) are only good at the time of clocking out the following morning (Friday morning).

Could you explain that a little more clearly? I've already started playing with your answer. SO ... also - these calculations need to be different and representative of each day of the week - correct? ... Thus 14 fields and calcs. - one for each of 7 StandardHours fields and 7 ShiftPayHours - right?

• ###### 3. Re: Need help on COMPLEX Time calculations

The calculations are actually good as long as Get ( CurrentDate ) returns a date that is for the same week. Come Sunday, the calculations return a timestamp for Thursday of the following week.

I don't see, from what you have posted so far, why you would need a different calculation for each day of the week. If you need the calculation to be for "midnight of the next day" instead of "midnight Thursday" as you originally specified, then a different calculation is needed that isn't "hardwired" to Thursday.

• ###### 4. Re: Need help on COMPLEX Time calculations

I DO NOT want the calculation to be "hardwired to" Thursday. It must be able to calculate on any one of all 7 days of the week including Sunday. Can you help me with that calculation?

This is all taking place because of a midnight shift on any given day that starts at 11:00pm at a rate of let's say \$10.00 from 11PM to midnight. Then the pay rate changes to let's say \$12.00 per hour from midnight to maybe 9:45AM the next morning.

However, I need the hours totals for each rate to be in separate fields - not combined into one field.

• ###### 5. Re: Need help on COMPLEX Time calculations

Am I correct then, that in each case of your original post where you said: "Thursday Midnight" you meant Midnight of the same day that they started work?

StandardHours:

Let ( MidnightTS = GetAsTimeStamp ( GetasDate ( TimeStampStart ) & " 12:00 am " ) ;
If ( MidnightTS < TimeStampEnd ; MidnightTS - TimeStampStart ; TimeStampEnd - TimeStampStart )
) / 3600

ShiftPayHours:

Let ( MidnightTS = GetAsTimeStamp ( GetasDate ( TimeStampStart ) & " 12:00 am " ) ;
If ( MidnightTS > TimeStampStart ; TimeStampEnd - TimeStampStart ; TimeStampEnd - MidnightTS )
) / 3600

• ###### 6. Re: Need help on COMPLEX Time calculations

Yes you are correct. This is a "Midnight Shift" situation by the employer. The StartTimeStamp is sometime in the 2 or 3 hours before Midnight of any one of the seven days of the week. 9.9999 times out of 10 the StartTimeStamp is always 11:00pm. Let's say 11:00PM Friday August 16th 2013 is when the employee clocks in. She works for 1 hour until midnight at a "StandardHours" pay rate of \$10.00 per hour (not actual - just example) for that one hour. Then beginning at 12:01AM on Saturday August 17th, 2013 the employees pay rate changes to a "ShiftPay" rate of \$12.00 per hour (not actual - just example) until she clocks out on Saturday morning at - let's say - 9:11AM.

This is a little program that I have set up for my wife to keep track of her hours "vs." the company she works for. It also incorporates a log feature where she can type necessary notes that she may want to document for future ammunition about activities that took place on her shift. She is a nurse. The administration is constantly making errors and incorrect judgement calls. So we are just creating our own human resources protection database.

A fun thing for me - a necessity for her.

That all being said - is the last calculation from you the one that should work for me?

• ###### 7. Re: Need help on COMPLEX Time calculations

You asked for two calculations, one for standard hours and one for graveyard shift hours. You have each in the previous post.

• ###### 8. Re: Need help on COMPLEX Time calculations

Thanks. Just wanted to make sure that I had not given you new information not dealt with in the calculations you gave me. I go to work on things now.

Once again, thanks for your help.