Discussions

 View Only
  • 1.  Calculate a date field with no weekends doesn't work correctly if starting date is on a weekend

    Posted 11-11-2019 08:20
    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
    ------------------------------


  • 2.  RE: Calculate a date field with no weekends doesn't work correctly if starting date is on a weekend

    Posted 11-11-2019 08:28
    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
    ------------------------------



  • 3.  RE: Calculate a date field with no weekends doesn't work correctly if starting date is on a weekend

    Posted 11-11-2019 11:17
    I'm getting an error:

    A variable declaration must end with a semi-colon.

    however, I tried adding one to no effect.

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



  • 4.  RE: Calculate a date field with no weekends doesn't work correctly if starting date is on a weekend

    Posted 11-11-2019 11:21
    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
    ------------------------------



  • 5.  RE: Calculate a date field with no weekends doesn't work correctly if starting date is on a weekend

    Posted 11-11-2019 11:23
    Edited by Evan Martinez 11-11-2019 11:23
    Hi Chris,

    What does your finished formula look like? Is the closing semi colon on the variable possibly behind the // in the formula Mark suggested and thereby getting ignored by the formula reader and greyed out? if so you may want to try:

    DayOfWeek([Date Shipped]) = 0,0, // Sunday
    1); // else 1

    Nevermind, I see in your response that you got it. 


    ------------------------------
    Evan Martinez
    Community Marketing Manager
    Quick Base
    ------------------------------