Populating a Date field using formulas

Torsten Rich-Wimmer

Hi All,

I have 2 known variables that I want to use to populate a Date field: We know the date that a Learner starts a course [Start_Date], and we know the cadence at which a report should be pulled afterward [Report_Cadence] (sometimes in weeks, sometimes in weeks + days). I am having trouble creating a formal that will populate a third Date field [Report_1_Date] based on the above information. The hope is to have it populate a date field x number of weeks and/or days in the future (and push weekends to the next weekday, but I figured out that bit already). Finally, I am at the beginning of development; so, I am open to any type of Column Type(s) for Report_Cadence. Any thoughts?

Example:

Input:

Start_Date: 01/01/21;

Report_Cadence: 1 Week & 1 Day;

Output:

Report_1_Date: 01/11/21 (Since Jan. 9 is a weekend)

Paul Newcome

I would suggest a table that has the options on the left and the number of days on the right then using an INDEX/MATCH.

=INDEX({Table Days Column}, MATCH([Report_Cadence]@row, {Table Selection Column}, 0))

Now that we have the number of days, we can add that to the Start Date like so:

=[Start_Date]@row + INDEX({Table Days Column}, MATCH([Report_Cadence]@row, {Table Selection Column}, 0))

Paul Newcome

Exactly how are you inputting the cadence? Can you provide some examples of what you would actually have in the sheet?

Torsten Rich-Wimmer

Hi Paul!

That is to be decided. It could be Text/Number, Single-Select, Multi-Select. Since I have not launched the tool, I am open to suggestions. My only request is that other users can update the cadence (which would ultimately change the output date).

Paul Newcome

My suggestion would be keeping it as consistent as possible. If you want to allow for weeks and days, then I would suggest separate columns for each where users only input a number. My personally preferred method would be only a single data type of days and people only enter the number. If you want to give users the ability to select an option such as "1 week + 1 day", then I strongly advise using a single select dropdown with the options prefilled and limited to only entering one of those options (no free text) to try to limit the variables. If you use free text, you are opening yourself up to so many different formats that it would be impossible to write a solution that accounts for every single one of them.

All 3 are going to be different solutions though, so until you determine exactly how the cadence is going to be entered all I can tell you is that it is possible depending on the formatting of the data in the cadence column.

Torsten Rich-Wimmer

Thank you, Paul.

Of your suggestions, we will need the flexibility of the second approach (my end-users would riot if I forced them to calculate 9+ months as days). At the moment, the variables we need immediately are:

1 week

1 week + 1 day

2 weeks

4 weeks

8 weeks

How would I best implement this and use a formula to populate the Date field?

Toufong Vang

For the greatest flexibility, you'll want to cross-reference a second sheet where you have the cadence--"1 Week", "1 week + 1 day", "2 weeks", etc.--in one column and the number of actual days in a second column (7, 8, etc.). Then use VLOOKUP() to match the selected cadence to the number of days and add that to the Start_Date.

Alternatively, you'll need a formula that:

• Parses the number of weeks from the string in Report_Cadence.

• Parses the number of days from the same string.

• Converts them into total number of days.

..so that you can calculate Start_Date + Report_Cadence to get Report_1_Date.

TO PARSE FOR NUMBER OF WEEKS

IFERROR(VALUE(LEFT(SUBSTITUTE([Report_Cadence]@row," ",""), Find("weeks",SUBSTITUTE([Report_Cadence]@row," ",""))-1)),0)

TO PARSE FOR NUMBER OF DAYS

IFERROR(VALUE(MID(SUBSTITUTE([Report_Cadence]@row," ",""), FIND("+", SUBSTITUTE([Report_Cadence]@row," ",""))+1, FIND("day",SUBSTITUTE([Report_Cadence]@row," ","")) - FIND("+", SUBSTITUTE([Report_Cadence]@row," ",""))-1)),0)

Report_1_Date = Start_Date + (num_weeks * 7) + num_days

= [Start_Date]@row + (IFERROR(VALUE(LEFT(SUBSTITUTE([Report_Cadence]@row," ",""), Find("weeks",SUBSTITUTE([Report_Cadence]@row," ",""))-1)),0)*7) + (IFERROR(VALUE(MID(SUBSTITUTE([Report_Cadence]@row," ",""), FIND("+", SUBSTITUTE([Report_Cadence]@row," ",""))+1, FIND("day",SUBSTITUTE([Report_Cadence]@row," ","")) - FIND("+", SUBSTITUTE([Report_Cadence]@row," ",""))-1)),0))

The statement SUBSTITUTE([Report_Cadence]@row," ","") removes the spaces from the cadence to make it easier to locate the numerals. The assumptions are that anything in front of "week" constitutes the number of weeks, and anything found between "+" and "days" is the number of days.

Hope this helps!

P.S., Adding months to the options will make any formula very, very messy and a bear to keep track of. It's still doable, but will be messier than a tub of spaghetti!

Paul Newcome

I would suggest a table that has the options on the left and the number of days on the right then using an INDEX/MATCH.

=INDEX({Table Days Column}, MATCH([Report_Cadence]@row, {Table Selection Column}, 0))

Now that we have the number of days, we can add that to the Start Date like so:

=[Start_Date]@row + INDEX({Table Days Column}, MATCH([Report_Cadence]@row, {Table Selection Column}, 0))