Forum Discussion

JenHamilton_Tye

Qrew Trainee

5 years ago

Subtract business hours from timestamp

I have a time stamp field called "Deadline" and I want to subtract business hours from it (weekday hours.) How? ToWeekdayP([Deadline]-Hours(#)) This doesn't work, it just knocks the result to a Fr...

Formulas and functions

tables and field

Qrew Member

5 years ago

I had to do something similar (calculate using working hours and not just regular hours). The way I did this was by creating a very complex set of variables and if statements that increased the hours to subtract depending on how to take into account the non-working hours. In my case this was in a single day so I only had to take into account an 8 hour work day. But you could do something similar using "DayofWeek" for your problem. It's brute force, but it works.

------------------------------
Andrew Patricio
------------------------------

JenHamilton_Tye
Qrew Trainee
5 years ago
I thought that might be the answer. I often feel like I'm making things more complex than they have to be, "WeekdayAdd" works so well on dates that I hoped there was an easier answer.

------------------------------
Jen Hamilton
------------------------------
- DonLarson
  Qrew Elite
  5 years ago
  Jen,
  
  Assuming:
  
  that you never exceed one weekend
  
  Deadlines are on the whole hour
  
  The amount of time you are going backwards is whole hours
  
  Try this:
  
  // Convert User Numeric Input to Duration in Hours
  var duration SubHours =Hours([Hours]);
  
  // Calculate Day of the Week from the Deadline Sunday = Day Zero, Saturday = Day Six
  var number DeadlineDay =DayOfWeek(ToDate([Deadline]));
  
  // Calculate the numerical Hour of the Day from the Deadline
  var number DeadlineHour = Hour(ToTimeOfDay([Deadline]));
  
  // Calculate Hours from Sunday
  var duration DeltaSunday = Hours($DeadlineDay-1)*24+ Hours($DeadlineHour);
  
  // If the hours to subtract is greater than the Delta to Sunday, subtract an extra 48 hours
  var datetime Early= If( $DeltaSunday>$SubHours, [Deadline]-$SubHours, [Deadline]-$SubHours- Hours(48));
  
  // Display the early date time stamp to the User
  $Early
  
  ------------------------------
  Don Larson
  Paasporter
  Westlake OH
  ------------------------------