Creating an Artificial downtime for maintenance for only week and work hours

gbrown

I am trying to calculate downtime within DOMO. The downtime we have currenlty coming from the other system does not put in the factors of weekends and holidays as well as after hours. How can I calculate a correct amount of time between the starttime to endtime while only subtracting within the window. For example if A start time on Friday and an endtime on that next Monday I would not like to account for the hours over the weekend but true business hours. I have tried multiple formulas and getting the same problem. Let me know your thoughts. Thanks

ST_Superman

@gbrown this was a fun problem to dive into.

Unfortunately, I do not believe this can be resolved with a calculated field. I used magic ETL. If you have access to the community Domo instance you can view my solution here. You can bug @jaeW_at_Onyx or @DataMavenLLC for access to the instance.

If you get to my page, scroll down to the bottom and you'll find this section:

Here is the overview though:

I created two constants for StartTime and CloseTime so that I could define the "working hours". Because it wasn't specified in the question, I chose 07:00:00 for the start time and 17:00:00 for the close time. this created a 10 hour work day.

For WorkingDays, Domo has a built in Date calculation in the "Date Operations" tile. (see image)

The issue I had was that if you start and end on the same day, then the WorkingDays was calculated as 0. I decided to add 1 to this value using the "Calculator" tile. 

Next, I had to calculate the DownTime. I broke this up into three scenarios:

• Jobs completed in the same day
  1. Using TIMEDIFF(`Completed`,`Assigned`) will tell me the time between the two values
  2. Then using TIME_TO_SEC(`totaltime`) / 3600 will give me the DownTime in hours

• Jobs completed in 2 working days
  1. For this, you need to calculate the time worked from the assigned time to closing on the first day
     • TIMEDIFF(`CloseTime`,TIME(`Assigned`))
  2. Then the time worked from the start of the second day to the completed time
     • TIMEDIFF(TIME(`Completed`),`StartTime`)
  3. Convert these times to seconds and divide by 3600
     • ( TIME_TO_SEC(`day1`) + TIME_TO_SEC(`day2`) ) / 3600

• Jobs greater than 2 days
  1. same as above, for the first and last day
  2. For mid days you need to add the hours in a workday times the full days worked

jaeW_at_Onyx

@ST_-Superman-_ i don't want to work through converting pseudo code to real code but i wonder if you could do math along the lines of

1. do the datetime diff. between start_date and close_date.
2. test if the day of week for CLOSE_DATE is < START_DATE (if it is we know there was a weekend in between and we have to subtract 48 hours from the datetime diff.

course… if there's a holiday, that adds a layer of complexity that rpobably could not be addressed without ETL.

ST_Superman

@jaeW_at_Onyx - He doesn't want to see the datetime difference though. He is looking for the amount of work hours between the two date time fields. So, if the workday is 9am-5pm. Then the time he would want for a job starting on Friday at 3pm and ending the following monday at 11am is 4. If you just did datetime diff, you would get 68 hours (minus the 48 hours for the weekend) and you have 20. Most of those hours were not during work hours.