3 Replies Latest reply on Jun 22, 2012 9:15 AM by philmodjunk

    Bad time card calc. function and can't fix it

    Dekade

      Title

      Bad time card calc. function and can't fix it

      Post

      FMProADV 10.0v3 Win7 x64 Professional

      I have tried to improve upon an already working database and have somehow goofed up two very similar calcualtions within the new update.

      Look at .png - DB1 - This a good working database acting as a time card. The two concerned fields here (that are now working nicely in this DB1 database) are the "AM Work Hours" and "PM Work Hours" fields.

      Here are the formulas for those two fields:

      AM Work Hours: Round(Hour( End Time AM - Start Time AM ) + Minute( End Time AM - Start Time AM ) / 60 - Lunch Hours;2)+If(GetAsNumber(End Time AM) <= GetAsNumber(Start Time AM);24;0)

      PM Work Hours: Round(Hour( End Time PM - Start Time PM ) + Minute( End Time PM - Start Time PM ) / 60 - Lunch Hours;2)+If(GetAsNumber(End Time PM) <= GetAsNumber(Start Time PM);24;0)

      Again - everything about DB1 is working well. It represents a time card that can record "1st Shift Hours Only" - AM and PM hours and total them -or- just record AM and PM hours seperately.

      Now look at .png - DB2 - I have tried to improve on DB1 because I need to have the availability to record "3rd Shift Hours" as well. DB2.png shows some strange field returns.

      Here is what I tried to make work for 3rd Shift hours worked recordings. I have altered the two field calculations as follows:

      Trial 3rd Shift PM Hours: Case ( IsEmpty ( Next Day Start PM ) and IsEmpty ( Next Day End PM ); Round(Hour( End Time PM - Start Time PM ) + Minute( End Time PM - Start Time PM ) / 60 - Lunch Hours; 2)+If(GetAsNumber(End Time PM) <= GetAsNumber(Start Time PM);24;0);
      IsEmpty ( Start Time PM ) and IsEmpty ( End Time PM ); Round(Hour( Next Day End PM - Next Day Start PM) + Minute( Next Day End PM - Next Day Start PM ) / 60 - Lunch Hours; 2)+If(GetAsNumber(Next Day End PM) <= GetAsNumber(Next Day Start PM);24;0))

       

      Trial 3rd Shift AM Hours: Case (IsEmpty ( Next Day Start AM ) and IsEmpty ( Next Day End AM ); Round(Hour( End Time AM - Start Time AM ) + Minute( End Time AM - Start Time AM ) / 60 - Lunch Hours; 2)+If(GetAsNumber(End Time AM) <= GetAsNumber(Start Time AM);24;0);
      IsEmpty ( Start Time AM ) and IsEmpty ( End Time AM ); Round(Hour( Next Day Start AM - Next Day End AM) + Minute( Next Day Start AM - Next Day End AM ) / 60 - Lunch Hours; 2)+If(GetAsNumber(Next Day Start AM) <= GetAsNumber(Next Day End AM);24;0))

      By looking at .png - DB2 you can see all the goofy returns I am getting. I'll admit I'm trying to get better at functions but still have a ways to go.

      Can anyone help me get this right?

      Thanks,

      Dekade

      DB1_DB2.png

        • 1. Re: Bad time card calc. function and can't fix it
          philmodjunk

          I suggest an approach that can lead to much simpler calculations:

          Use timestamp fields instead of just time fields.

          Because the fields store date and time, the difference between them will compute elapsed time in seconds without regard to whether time falls in am or pm portions of the day.

          Round ( TimeStampEnd - TimeStampStart ) / 3600 ; 2 )

          will give you elapsed hours to two decimal places even if Time end falls on the next day.

          • 2. Re: Bad time card calc. function and can't fix it
            Dekade

            Hi PhilModJunk,

            Someday I'd love to hear what your Forum ID stands for.

            Thanks so much for the answer you just gave to me. I don't think I can get at trying it tonight but will for certain first thing in the morning. If I again have troubles I will again post.

            You need to know the following - As for your politeness, business approaches via your answers, and non belittling replies - I thank you for your presence in this forum. Very nice attributes in this day and time.

            Dekade

             

            • 3. Re: Bad time card calc. function and can't fix it
              philmodjunk

              Others have asked about my forum name and the story is rather mundane. Back when I joined the forum, most posters had forum names that did not include their specific names. I work for a company named "Modesto Junk Company" and thus came up with the Moniker that I use here (Phil is my first name) as a subtle way to promote the name of the company I work for.