Announcement

Collapse
No announcement yet.

calculate time between two date fields

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • calculate time between two date fields

    i have in cases entity, two fields, start time and end time. how can i have espocrm tell me the amount of time between start time and end time?
    Example: start time = 2:00AM, end time = 2:10AM, total time = 00:10

  • #2
    By utilizing formla:

    totalTimeMinutes = (datetime\format(endTime, 'UTC', 'U') - datetime\format(startTime, 'UTC', 'U')) / 60;

    Where totalTimeMinutes - Integer field;
    Last edited by yurikuzn; 03-19-2018, 01:46 PM.

    Comment


    • #3
      when i close a case, it just shows 0 in totalTimeMinutes when i apply the formula.

      Comment


      • #4
        Fixed:

        totalTimeMinutes = (datetime\format(endTime, 'UTC', 'U') - datetime\format(startTime, 'UTC', 'U')) / 60;

        Comment


        • #5
          that works, but it only shows a whole number. so if the time is 7 minutes it shows as 7 not a decimal. the / 60 is not working.

          Comment


          • #6
            what i mean is that if 240 seconds / 60 = 4, then i need it to take 4 /60 again to give me decimal 0.66

            Comment


            • #7
              This is a simple math.

              floor(270 / 60) = 4 hours

              (270 / 60 - floor(270 / 60)) * 60 = 30 minutes


              Code:
              $durationMinutes = (datetime\format(endTime, 'UTC', 'U') - datetime\format(startTime, 'UTC', 'U')) / 60;
              
              $hours = number\floor($durationMinutes / 60);
              $minutes = ($durationMinutes / 60 - $hours) * 60;
              
              duration = string\concatenate($hours, ':', $minutes);
              Note: I didn't test it.
              Last edited by yurikuzn; 03-20-2018, 04:25 PM.

              Comment


              • #8
                yes i know the simple math, but i didnt know the code the way espocrm wants it written. Thnaks

                Comment


                • #9
                  Hi Guys

                  Added formula to entity as follows:-

                  ​​​​​​totalDrivingTime = (datetime\format(tripEndWK1, 'UTC', 'U') - datetime\format(tripStartWK1, 'UTC', 'U')) / 60;

                  This gives me the total minutes successfully, however if this goes over an hour i would like to display Hours & Minutes as well as taking into account going over midnight. Can someone help me with what i need to add to this line?

                  Thanks guys
                  Last edited by Maximus; 12-02-2019, 07:19 AM. Reason: Hi,

                  Comment


                  • Maximus
                    Maximus commented
                    Editing a comment
                    Hi,
                    here is a sample of how you can count the hours and minutes in ​​​​​​totalDrivingTime (​​​​​​totalDrivingTime should be a varchar type). Use this Formula:
                    Code:
                    $totalTime = (datetime\format(tripEndWK1, 'UTC', 'U') - datetime\format(tripStartWK1, 'UTC', 'U')) / 60;
                    $hours = $totalTime / 60;
                    $minutes = $totalTime % 60;
                    ​​​​​​totalDrivingTime = string\concatenate($hours, ' hour(s) ',$minutes, ' minute(s)')
                    Note: I didn't test it.
                    Last edited by Maximus; 12-02-2019, 07:28 AM.

                • #10
                  Hi Maximus,

                  Your a star, many thanks for your help. Not sure what to tweak as its not quite there, as you look at the screenshot. It needs to read 1 hour 12 minutes in totalDrivingTime. Anyway i think where i was going wrong is that i had it as an integer field, since changed to varchar and seems to be light at the end of the tunnel. If you can help me with the final tweak that would be great.

                  Thank you so much for your help, it's much appreciated.

                  Comment


                  • #11
                    This should work:
                    Code:
                    $totalTime = (datetime\format(dateEnd, 'UTC', 'U') - datetime\format(dateStart, 'UTC', 'U')) / 60;
                    $hours = number\floor($totalTime / 60);
                    $minutes = ($totalTime / 60 - $hours) * 60;
                    totalDrivingTime = string\concatenate($hours, ' hour(s) ',$minutes, ' minute(s)')

                    Comment


                    • #12
                      Absolutely Spot on, works a treat... Thanks Maximus your help has saved the last strands of hair i have left... Thanks its much appreciated..

                      Comment

                      Working...
                      X