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

kylebible

I have a table w/ 2 columns with timestamps. I want to calculate the difference between these 2 times in business hours, so only count the hours between 9-17 on weekdays.

kshah008

Hi all,

Can anybody help @kylebible with their question?

Thanks!

zcameron

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!

 

kylebible

Wow this is awesome!! So there are actually quite a few cases where the start time is outside of 9-5, due to people working after hours. If I wanted those entries to basically just become 9 on the next business day, I can think of a basic case statement that would work if it was before 9 on m-f, but if it's after 5, I'm not sure how I'd carry it over to the next day at 9? Especially if it's after 5 on a friday.

 

I get at a basic level pretty much everything that's going on here except for the matrix, that confused me.

 

Thanks so much for your help on this!

kylebible

Could I maybe do a case when time is <9 then 9, and case when time is >5 then 5, case when day is saturday or sunday then monday at 9?

kylebible

Here is what I came up with:

 

CASE DATEDIFF(SUBTIME(`Completed_Date__c`,28800), SUBTIME(`CreatedDate`,28800))
WHEN 0 THEN
ROUND((HOUR(SUBTIME(`Completed_Date__c`,28800)) + (MINUTE(SUBTIME(`Completed_Date__c`,28800))/60)) - (HOUR(SUBTIME(`CreatedDate`,28800)) + (MINUTE(SUBTIME(`CreatedDate`,28800)) / 60)))
ELSE
ROUND(
(17 - (Case when HOUR(SUBTIME(`CreatedDate`,28800)) < 9 then 9
when HOUR(SUBTIME(`CreatedDate`,28800)) > 17 then 17
when HOUR(SUBTIME(`CreatedDate`,28800)) = 9 then 9
when HOUR(SUBTIME(`CreatedDate`,28800)) = 10 then 10
when HOUR(SUBTIME(`CreatedDate`,28800)) = 11 then 11
when HOUR(SUBTIME(`CreatedDate`,28800)) = 12 then 12
when HOUR(SUBTIME(`CreatedDate`,28800)) = 13 then 13
when HOUR(SUBTIME(`CreatedDate`,28800)) = 14 then 14
when HOUR(SUBTIME(`CreatedDate`,28800)) = 15 then 15
when HOUR(SUBTIME(`CreatedDate`,28800)) = 16 then 16
when HOUR(SUBTIME(`CreatedDate`,28800)) = 17 then 17 end
+ (MINUTE(SUBTIME(`CreatedDate`,28800))/60)))
+
(
5 * FLOOR(DATEDIFF(SUBTIME(`Completed_Date__c`,28800), SUBTIME(`CreatedDate`,28800)) / 7)
+
CASE SUBSTRING('0012345400123433001232230012112300100123000012340', 7 * ((Case when WEEKDAY(SUBTIME(`CreatedDate`,28800) ) in (5,6) then 4 when WEEKDAY(SUBTIME(`CreatedDate`,28800)) = 0 then 0 when WEEKDAY(SUBTIME(`CreatedDate`,28800)) = 1 then 1 when WEEKDAY(SUBTIME(`CreatedDate`,28800)) = 2 then 2 when WEEKDAY(SUBTIME(`CreatedDate`,28800)) = 3 then 3 when WEEKDAY(SUBTIME(`CreatedDate`,28800)) = 4 then 4 end) - 1) + (Case when WEEKDAY(SUBTIME(`Completed_Date__c`,28800) ) in (5,6) then 4 when WEEKDAY(SUBTIME(`Completed_Date__c`,28800)) = 0 then 0 when WEEKDAY(SUBTIME(`Completed_Date__c`,28800)) = 1 then 1 when WEEKDAY(SUBTIME(`Completed_Date__c`,28800)) = 2 then 2 when WEEKDAY(SUBTIME(`Completed_Date__c`,28800)) = 3 then 3 when WEEKDAY(SUBTIME(`Completed_Date__c`,28800)) = 4 then 4 end), 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
+
((Case when HOUR(SUBTIME(`Completed_Date__c`,28800)) < 9 then 9
when HOUR(SUBTIME(`Completed_Date__c`,28800)) > 17 then 17
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 9 then 9
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 10 then 10
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 11 then 11
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 12 then 12
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 13 then 13
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 14 then 14
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 15 then 15
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 16 then 16
when HOUR(SUBTIME(`Completed_Date__c`,28800)) = 17 then 17 end + (MINUTE(SUBTIME(`Completed_Date__c`,28800))/60)) - 9)
)
END

 

For some reason the time code is off by 8 hours in my data, so I had to subtract 8 hours from each one in order to get the actual time submitted.

zcameron

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

kylebible

Also just noticed since the WEEKDAY() function has monday as 0, and we're subtracting 1 from the start time, it seems like, for example, if the start day is monday(0), and the end day is friday(4) then the function would take

7*(0-1)+4 giving us -3 as our starting position in the substring which won't work?

 

would this make more sense?

 

  MTWTFSS
 |-------
M|0012333
T|3001233
W|2300122
T|1230011
F|0123000
S|0123400

S|0123450

 

So I'd change that section to

 

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

 

zcameron

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

kylebible

In beast mode Weekday() is starting w/ monday as 0

zcameron

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.

[Deleted User]

@kylebible did any of the replies solve the original question? If so please clicnk on "accept as solution".

 

Thanks!
Dani

user09708

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

KevinNorman

Hello Mr zcameron,

 

I found this post extremely helpful. Would it be possible to get on the phone to talk about this formula? I don't know how much support you're allowed to give but I'd really like to understand.

A couple of my questions:

 

ELSE
ROUND(
(17 - (HOUR(`updated_at`) + (MINUTE(`updated_at`)/60)))

- Why 17?

- If I wanted to measure the number of hours passed each day not including weekends (I don't care to account for holidays) then would I simply change

WHEN '5' THEN 5
      END
      ) * 8

to 

WHEN '5' THEN 5
      END
      ) * 24

?

zcameron

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!