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

    Need help on COMPLEX Time calculations

    Dekade

      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".

           Can someone please help me on this complex calculating?

           Thanks for your help,

           Dekade

            

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

               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
            Dekade

                 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?

                 Dekade

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

                   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
                Dekade

                     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
                  philmodjunk

                       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
                    Dekade

                         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
                      philmodjunk

                           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
                        Dekade

                             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.