10 Replies Latest reply on Feb 6, 2014 12:40 PM by EdwardAlvarez

    Calculating hours

    EdwardAlvarez

      Title

      Calculating hours

      Post

           Hi,

           I am trying to auto calculate the staff hours on our contracts. we currently have a drop down list where we pick the hours. i want to automate this and make a formula calculate it on its own. i have this formula that is working great; however, it only works until 11:59pm. once i enter 1am for example, the formula does not calculate the hours anymore. 

           In our business we usually go over 12am, we often work until 1am or 2am and start around 6pm-9pm. so if i have an event from 5pm-1am it should calculate 8 hours but it won't calculate the hours correctly it instead gives me a negative number (-15). if i enter 5pm-10pm for example it calculates correctly and it gives me 5 hours. what am i doing wrong?

           This is the formula i am currently using:

      If(GetAsNumber(Valet Start Time:) <  GetAsNumber(Valet End Time:); Hour( Valet End Time: - Valet Start Time: ) + Minute( Valet End Time: - Valet Start Time:)/60; Hour( Valet End Time:+24 - Valet Start Time: ) + Minute( Valet End Time: - Valet Start Time: )/60)

           Thank you

           Edward

      Screen_Shot_2014-02-05_at_4.06.13_PM.png

        • 1. Re: Calculating hours
          EdwardAlvarez
          /files/e6f35ec1a9/Screen_Shot_2014-02-05_at_4.00.31_PM.png 300x153
          • 2. Re: Calculating hours
            jmw

                 Sometimes it helps to see the values of parts of the calculation: Valet End Time + 24 gives 1:00:24 instead of the 25:00:00 you probably expected. Have a look at the Time ( hours ; minutes ; seconds ) function. If you change the 24 to Time ( 24 ; 0 ; 0 ) things already look different.

                 Also; I would like to suggest to not use the ':' character in a field name. Although valid, it comes quite close the format of using a table with a field: tablename::fieldname. The ':' in a field name might lead to confusion later (or for others) in that there might be something missing. Just my own preference, nothing more.

                 As a side note, I would probably calculate the start and end hours separately and then compare them, something like this:

                 Let([
                 _HoursValueStart = Hour( Valet Start Time: ) + Minute( Valet Start Time:)/60; 
                 _HoursValueEnd  = Hour( Valet End Time: ) + Minute( Valet End Time:)/60
                 ];
                  
                 If(_HoursValueStart < _HoursValueEnd; 
                 _HoursValueEnd - _HoursValueStart; 
                 _HoursValueEnd + 24 - _HoursValueStart
                 )
                 )
                  
                 Anyway, my two cents...
            • 3. Re: Calculating hours
              philmodjunk

                   To calculate elapsed time in hours, use this formula as it is much simpler:

                   ( TimeEnd - TimeStart ) / 3600

                   When you subtract two time fields, you get the elapsed time in seconds and just need to divide by 3600 seconds per hour to get hours.

                   To accurately compute elapsed time when the intervals starts before midnight but ends after midninght, use TimeStamp fields instead of time fields.

                   (TimeStampEnd - TimeStampStart ) / 3600

                   There are a number of ways that you can compute a time stamp from separate date and time fields.

              • 4. Re: Calculating hours
                EdwardAlvarez

                     it works perfect when i make a TimeStamp field. however I am require to enter the date and what i am trying to avoid is to minimize the typing. it also shows the entire date and time, i only need it to show the time. is this possible?

                     Thankyou


                     EA

                • 5. Re: Calculating hours
                  philmodjunk

                       To repeat: If there are separate date and time fields, it's quite easy to set up a calculation to use the data and time to produce a time stamp for your elapsed time calculation.

                       ( TimeStamp ( DateField2 ; TimeField2 ) - TimeStamp ( DateField1 ; TimeFIeld1 ) ) / 3600

                  • 6. Re: Calculating hours
                    EdwardAlvarez

                         that is my issue, we do not include the end date on the contact, we only include the date of the event. is there something i could do that it won't visible but it would automatically add the end date if the event starts on 2/7/14 at 6pm and is ending at 1am on 2/8/14 for example?

                          

                         Thank you

                         EA

                    • 7. Re: Calculating hours
                      philmodjunk

                           ( If ( Time2 < TIme1 ; TimeStamp ( DateField + 1 ; Time2Field ) ; TimeStamp ( DateField ; Time2Field ) ) - TimeStamp ( DateField ; Time1Field ) ) / 3600

                      • 8. Re: Calculating hours
                        EdwardAlvarez

                             let me see if i understand this correctly:

                             Time fields will be my current "START TIME" and "END TIME" where they both will be TIME fields. then i will need another set of Start/End time fields but in this case they will be TimeStamp fields?

                             Thank you

                             EA

                        • 9. Re: Calculating hours
                          philmodjunk

                               There's a typo in what I posted. I didn't notice that I was changing the name of the fields halfway through writing the expression. There are only three fields used in this calculation:

                               ( If ( Time2 < TIme1 ; TimeStamp ( DateField + 1 ; Time2 ) ; TimeStamp ( DateField ; Time2 ) ) - TimeStamp ( DateField ; Time1 ) ) / 3600

                               Time2 = End Time and Time1 = Start Time.

                          • 10. Re: Calculating hours
                            EdwardAlvarez

                                 Thank you much Phil! you are a genius! 

                                 How can i treat you to a cup of coffee!

                                 Ed Alvarez