You are viewing limited content. For full access, please sign in.

Question

Question

Working Hours Time Calculator

asked on August 1, 2019

Hey everyone,

I'm wondering if it's possible to utilize workflow to determine a four-hour window for me. You can easily do this within Forms or Workflow with the Timer Event or Delay Task specifically, however, this does not account for WORKING hours... only four hours from the start of the task, excluding weekends.

I need someone smarter than me to show me how this could be accomplished!

I'll give two examples.

If the task was started on a Monday, at 3 PM, I'd want workflow to return a value of the following Tuesday, at 10 AM (four working hours later, assuming an 8 - 5 schedule).

If the task was started on a Friday, at 4 PM, I'd want workflow to return a value of the following Monday, at 11 AM (four working hours later, same 8 - 5 schedule, excluding the weekend).

Bonus points for taking holidays into effect. But to have that calculation mapped out for me would be huge - so thank you, in advance, if you're aware of how to make that calculation happen!

0 0

Replies

replied on September 26, 2019 Show version history

I started putting in the logic for this and then I got sidetracked.

I was working off a hardcoded "test date" token to make testing easier, so you'd want to replace that with your field or form variable. I also assumed the "test date" is going to be on the current day. The logic should still work for an arbitrary date, but you may have to tweak the other tokens, like "EndOfToday" a bit to account for that.

I extracted the day of the week from the date to make it easier to check for weekends ("ddd" formatting on a date token will give you the short day name according to the OS language- "Sat", "Sun", etc). The first branch in the conditional decision checks if the date is either a Saturday or a Sunday and then sets the deadline 4 hours after the start of the day on Monday. So "next monday at 12PM".

Then we're in a weekday and we have 2 possibilities: we're either more than 4 hours from the end of the day (and we can just add 4 hours to the current date) or we're less than 4 hours to the end of the day (and things get hairy).

So first I calculated how far off we're from the end of today (end of the day is hardcoded to 5PM on the test date entered. You could extend this for variable schedules by pulling the end of the day from a SQL table or something). So DayPart just subtracts the test date from the end of the day date. This returns a fractional part of day. You can work with it as such and use 1/6 of day for 4 hours, but that sort of math was hard so I converted it to hours by multiplying it with 24.

If we have more than 4 hours before the end of the day, then add 4 hours to the current date and time.

If we have less than 4 hours before the end of the day, then it gets a bit complicated. The date is going to be the next work day (so we can use the WorkDay function to skip weekends). For the time, I can calculate how many hours we're going past, but I can't add a fractional number of hours to a datetime. For ex, if it's 2:45 PM, I'm 2h 15 min from 5PM and that's 2.25 hours, so we're left with 1.75 hours for the next day. You can imagine how that gets more complicated if it's 2:17PM or some other such time that results in a number with more decimals. So we have to convert this to hours and minutes and do some rounding.

So

TimeRemaining = 4 - %(TokenCalculator_TimeLeft)   

(how much we're overflowing into the next work day - as hours, possibly with decimals)

Hours = FLOOR(%(TokenCalculator2_TimeRemaining))

(hours part of TimeRemaining - the integer part of the value above)

MinutesLeft = Round(60*(%(TokenCalculator2_TimeRemaining)-%(TokenCalculator2_HoursLeft)))

(the decimal part of TimeRemaining converted to an integer number of minutes - so multiply by 60 and round. Add 1 to account for rounding down and give them an extra minute instead of taking away a few seconds if you're feeling generous)

Then, using Date Token Calculator, we're using the beginning of the day on the next work day at 8AM and adding the number of hours and minutes we just figured out above to get the deadline.

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.