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

Performing a calculation based on a time condition

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

Hi Richard,

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

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.

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.

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

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

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

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