Future Date Formula

Question

I am working on developing an automatic job scheduler for our operations team. In my app, I have two fields:

[Most Recent Production Run Date] and [Recurrence]

From these two fields, I need to be able to calculate the [Next Scheduled Production Run Date].

I have been able to figure out for monthly, weekly, annual jobs, but where I am struggling is on jobs that we run, for example, twice monthly... in these cases, for example, [Recurrence]="Mid-Month: 16th and 23rd". So, if the [Most Recent Production Run Date]=1/16/2021, then I would need the [Next Scheduled Production Run Date] to be 1/23/2021. If the [Most Recent Production Run Date]=1/23/2021, then I would need the [Next Scheduled Production Run Date] to be 2/16/2021.

Any advice on how to build a formula to achieve this result?

Thanks, Jen

------------------------------
Jennifer Miller
------------------------------

BlakeHarrison · Answer

Well, based on what you've said, your formula is going to be a bit of a bear because you'll have to setup each option under 'Recurrence' with multiple output values (see the CASE formulas below). With that said, for the selection you've identified, I would suggest this:

var number one = Case([Recurrence],
                         "Mid-Month: 16th and 23rd",16,
                         "Second Option",{1st number of 2nd Option},etc..);

var number two = Case([Recurrence],
                         "Mid-Month: 16th and 23rd",23,
                         "Second Option",{2nd number of 2nd Option},etc..)

var number d = Day([Most Recent Production Run Date]);
var number m = Month([Most Recent Production Run Date]);
var number y = Year([Most Recent Production Run Date]);
var number dd = IF($d = $one,$two,$one);
var number mm = IF($d = $one,$m,Month(AdjustMonth([Most Recent Production Run Date],1);
var number yy = IF($d = $one,$y,Year(AdjustMonth([Most Recent Production Run Date],1);

Date($yy,$mm,$dd)

------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quickbase Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------

Sign up

Login to the community