How can I show an age between 2 time stamps in business hours?

I believe this function, written to work as a beast mode calculation, should get you what you're looking for:

-- ---------------------------------------------------------

CASE DATEDIFF(`end_datetime`, `start_datetime`)
  WHEN 0 THEN
    ROUND((HOUR(`end_datetime`) + (MINUTE(`end_datetime`)/60)) - (HOUR(`start_datetime`) + (MINUTE(`start_datetime`) / 60)))
  ELSE
    ROUND(
      (17 - (HOUR(`start_datetime`) + (MINUTE(`start_datetime`)/60)))

      +

      (
      5 * FLOOR(DATEDIFF(`end_datetime`, `start_datetime`) / 7)

      +

      CASE SUBSTRING('0012345400123433001232230012112300100123000012340', 7 * (WEEKDAY(`start_datetime`) - 1) + WEEKDAY(`end_datetime`), 1)
        WHEN '0' THEN 0
        WHEN '1' THEN 1
        WHEN '2' THEN 2
        WHEN '3' THEN 3
        WHEN '4' THEN 4
        WHEN '5' THEN 5
      END
      ) * 8

      +

      ((HOUR(`end_datetime`) + (MINUTE(`end_datetime`)/60)) - 9)
    )

END

-- -----------------------------------------------------------

It's a bit complex, so I'll walk through it step by step to help make it clearer what's going on.

First, we're using a CASE statement to handle the special situation where the start_datetime and the end_datetime are on the same date. In that case, we just want to see how much time there was between the timestamps. To find that information, we're converting the minutes from each timestamp to decimals and adding them to the hours from each timestamp. Then we're subtracting the two and rounding the difference to the nearest hour.

In the more general case, we're finding how many hours (and decimal parts of hours again) were left in the day after the start_datetime.

1. Next, we'll add 8 hours for each day that has passed between the two dates. We do this in two parts:
   Calculate the number of complete weeks passed between the two dates and multiply it by 5 working days for each of those weeks.
   
   
2. Find how many working days there would be left over based on what day of the week the start and end dates fall on. This is the most complex part of this calculation. To get this to work, we create a 7 x 7 matrix, one value for each combination of a start and end day of the week. The matrix would look like this

  SMTWTFS
 |-------
S|0012345
M|4001234
T|3300123
W|2230012
T|1123001
F|0012300
S|0012340

Each row represents the start day of the week, and each column represents the end day of the week, beginning with Sunday and going through Saturday. If, for example the start date were on a Monday, and the end day were on a Thursday, we would go row 2 (Monday is the second day) and across to column 5 (Thursday is the 5th day) and find the number 2, meaning there are two working days between Monday and Thursday, namely Tuesday and Wednesday. We can't do this sort of row x column lookup in a beast mode, so instead, we flatten the matrix into a single string and use multiplication to arrive at the correct value for the start date and end date combination - 7 * the day of the week for the start date, minus one so that we can access the first 7 elements if we are starting on the first day of the week, then we add the day of the week of the end date to help us land on the right value. This multiplication value is used with the SUBSTRING function to extract the nth element from the string. Beast mode doesn't have a way to directly convert that string value into a integer, so we use a case statement to do it for us. If we landed on the string '3', the CASE statement will return the number 3, etc.

Now we take the 5 days per week, add the extra days we found from our matrix and multiply them by 8 hours a day.

Finally, we add the hours from the end_datetime until the end of the business day and round it all to the nearest hour.

Some limitations to this function:

This expression does not account for timestamps that fall outside the working day or the working week. If you wanted to, you could deal with these possible situations in a case statement, adjusting your formulas accordingly.

This expression does not account for holidays. Figuring holidays into the equation would be a much larger problem than could be solved with a beastmode. It could be done, however, in a MySQL dataflow if you could include a separate table that contained dates of holidays for the time periods your data could cover.

This function could be reworked to run in a MySQL dataflow. The matrix would need to be reworked as well as the system for indexing into it because the WEEKDAY function behaves a little differently there. Monday = 0, Tuesday = 1, etc. So the matrix would look like this:

 |MTWTFSS

 |-------

M|0012344

T|3001233

W|2300122

T|1230011

F|0123000

S|0123400

S|0123440

So the substring function would look like this:

SUBSTRING('0012344300123323001221230011012300001234000123440', 7 * WEEKDAY(`start_datetime`)  + WEEKDAY(`end_datetime`), 1)

Please note that this function is provided as a best-guess starting point and should to be thoroughly tested before use in any production system.

Please let me know if that helps!

Creative! Is it giving you the result you expect? 

Depending on how you want it to behave, you could also flip the case to the other part of the expression like this:

CASE WHEN HOUR(`CreatedDate`) < 17 THEN  <ADD HOUR AND MINUTES TOGETHER> ELSE 0 END

Which would say that if it falls before the end of the normal day, then calculate it as normal, otherwise, don't add any work hours because it happened outside of work hours.

Similarly, you could flip the other end of it too like this:

CASE WHEN HOUR(`Completed_Date__c`) > 9 THEN <normal calcuation> ELSE 0 END

Are you looking at it in beast mode or in SQL? WEEKDAY behaves a little differently in the two environments.

If your instance set up that way, you're right. You'd need to rework the indexing on the substring to match how it's returning weekday values. Nice call.

Thanks for your hard work on this. We've incorporated it into our use case.

The 17 comes from the original question. The poster only wanted to include hours between 9 and 5, or 09:00 and 17:00.

You're right about switching to "* 24". The "* 8" part of the formula was assuming 8 working hours per day.

It sounds like you've got a pretty good handle on it!