
1. Re: Performing a calculation based on a time condition
Soumitra Godbole Feb 7, 2020 12:01 PM (in response to Richard S.)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

Richard's Data.xlsx 271.0 KB

2. Re: Performing a calculation based on a time condition
Richard S. Feb 8, 2020 8:46 AM (in response to Soumitra Godbole)1 of 1 people found this helpfulThank 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!

3. Re: Performing a calculation based on a time condition
Richard S. Feb 16, 2020 5:59 PM (in response to Richard S.)1 of 1 people found this helpfulHi 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