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

Question

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
------------------------------

MarkShnierYou · Answer

Here is one solution.var number DaysToAdd = If(DayOfWeek([Date Shipped]) = 6,0, // SaturdayDayOfWeek([Date Shipped]) = 0,0, // Sunday1) // else 1WeekDayAdd([Date Shipped],$DaysToAdd)------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

Sign up

Login to the community