Forum Discussion

JenHamilton_Tye's avatar
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 Friday if it falls on Sat/Sun.
Subtracting 36 business hours from an 8am Monday deadline should return Thursday at 8pm, not Friday at 8pm.

------------------------------
Thanks,
Jen Hamilton
------------------------------
Formulas and functions
tables and field
  • AndrewPatricio's avatar
    AndrewPatricio
    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's avatar
      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's avatar
        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
        ------------------------------