Forum Discussion

ChrisNewsome's avatar
ChrisNewsome
Qrew Captain
6 years ago

Calculate a date field with no weekends doesn't work correctly if starting date is on a weekend

OK folks, so I'm tracking shipments that our manufacturing facility ships via our own trucks to another branch. These shipments are always delivered the next business day. Currently, I have a date field that they enter when they ship it (i.e., 11/08/2019) and I have a formula field that takes that field and adds 1 day, but only weekdays. It looks like this:

WeekdayAdd([Date Shipped],1)

Today I found a slight bug in my setup, in that the shop came in on Saturday (not normal) and worked, so when they marked it "shipped" on 11/09/2019, the calculated field jumped to "11/12/2019" instead of "11/11/2019" when it is actually being delivered. We have a notification email that goes out to the branch manager every day with a report of what's been shipped, so today there was no report as everything is scheduled for tomorrow - but they are getting the delivery today.

Is there a way to craft the formula so that even if the start date is a weekend, the calculated date is the next weekday?

------------------------------
Chris Newsome
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Here is one solution.

    var number DaysToAdd = If(
    DayOfWeek([Date Shipped]) = 6,0, // Saturday
    DayOfWeek([Date Shipped]) = 0,0, // Sunday
    1) // else 1

    WeekDayAdd([Date Shipped],$DaysToAdd)

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • ChrisNewsome's avatar
      ChrisNewsome
      Qrew Captain
      6 years ago
      I'm getting an error:

      A variable declaration must end with a semi-colon.

      however, I tried adding one to no effect.

      ------------------------------
      Chris Newsome
      ------------------------------
      • ChrisNewsome's avatar
        ChrisNewsome
        Qrew Captain
        6 years ago
        Figured it out. I was putting the semi colon  in the wrong spot:

        var number DaysToAdd = If(
        DayOfWeek([Date Shipped]) = 6,0, // Saturday
        DayOfWeek([Date Shipped]) = 0,0, // Sunday
        1) // else 1
        ;
        WeekDayAdd([Date Shipped],$DaysToAdd)

        Works great now! Thanks again Mark!

        ------------------------------
        Chris Newsome
        ------------------------------