3 Replies Latest reply on Feb 16, 2020 5:59 PM by Richard S.

    Performing a calculation based on a time condition

    Richard S.

      Hello,

       

      I am trying to do a calculation where I would calculate the cost of an event duration provided it fits within the time parameters, specifically the cost of a meeting if it falls within a certain time of between 8 am (or 480 minutes) - 4 pm (or 960 minutes).  This is what's known as the core hours.

       

      If the meeting starts before 8 am and ends before 8 am, it is to be excluded.

      If the meeting starts after 4 pm and ends before 8 am the next day, it is to be excluded.

      If the meeting starts before 8 am and the end of the meeting falls between 8 am and 4 pm, the duration should only include the minutes between 8 am and 4 pm

           (i.e. if meeting is from 7 am to 10 am, instead of 3 hours between, it should only be 2 hours duration because it's only including the hours between 8 am and 10 am)

           (i.e. if meeting is from 1 pm to 7 pm, instead of 6 hours between, it should only be 3 hours duration because it's only including hours between 1 pm and 4 pm)

       

      The total hours is then multiplied by a cost amount.

       

      Any one willing to take a stab at this?  I have attached the workbook.  Thanks!

        • 1. Re: Performing a calculation based on a time condition
          Soumitra Godbole

          Hi Richard,

           

              I did take a stab (at the problem that is ) and here is my simple solution below :

          Dashboard.png

          I came up with a strategy using the rules proposed (regarding the duration), and after verifying that all

          meetings got over before 11:59:59pm and started at midnight (12:00:00am) and there was no carry over

          from one day to another (thankfully no late nights ). Here are a few calculated fields that did the trick.

           

          Formula 1 - 8am.png

          Formula 2 -  4pm.png

           

          Note: I had to add the -2 as a buffer to compare apples to apples as your Start & End times. There was

          some adjustment needed when using time only (without a date). As per excel the first day or Day 1 is

          1st Jan 1900 so day 0 will be Dec 31st, 1899.

           

          Formula 3 - Effective Start Time.png

           

          Formula 4 - Effective End Time.png

              This is what you used to calculate the duration (which highly overestimates the duration since it

              includes the times outside the work hour range (8m to 4pm).

           

           

          Formula 5 - Duration (Apparent).png

           

          Formula 6 - Duration (Actual).png

                This is really the magic formula that does the trick by excluding times outside the work hours and

                the IFNULL gives them a 0 value.

           

           

           

                In addition to this I created a small summary for the cost (multiplying it by $ 2.25 which is the rate you

                used). I have also shown you the estimated cost (actually overestimated cost) along with the cost

                savings.

           

                For your reference I have attached the twbx file and a dummy data spreadsheet (I got after exporting

                your datasource to excel). In your example, you will need to change the string values for duration into

               actual datetime values since we need to calculate the total.

           

              Let me know if you have any questions or need further clarity with any of the calculations. Hope the

              above  exercise was easy to follow, entertaining and correctly answered your question. Best wishes !

           

              Sincerely,

           

              Soumitra

          • 2. Re: Performing a calculation based on a time condition
            Richard S.

            Thank you Soumitra!  I will take a look at this on either Monday or Tuesday, but I just wanted to say thank you for taking the time to reply.  It looks very detailed and what I am looking for.   I will let you know if I have any questions when I look at it more on Monday.  Thank you and have a great weekend!

            1 of 1 people found this helpful
            • 3. Re: Performing a calculation based on a time condition
              Richard S.

              Hi Soumitra,

               

              I apologize for the delayed response.  Yes, your solution is what I needed perfectly.  Thank you for taking the time to provide your solution!

               

              Best,

              Richard

              1 of 1 people found this helpful