I would like to link an actual calendar date to each multiple select item.
In my table, each record is sorted by DAYS (DAY 1, DAY 2 etc). The actual calendar date of the DAYS may shift in the course of my work. When this happens I would like to be able to change the actual calendar date, or DAYS that the record will now take place so that it is correctly plotted on the calendar view.
Can I create a drop down list of items that correlate to additional data?
Let me make sure I understand your goal clearly. It sounds like you’re plotting individual steps of a multi-step process, and want to use the DAYS field to control the date on which each step will be done. For example, if your process begins on January 1, setting a certain step to be Day 1 would mean it’s to be done on January 1, setting it to Day 2 would be January 2, etc. For example:
However, if your process needs to shift to start on January 15th, you want all of the Day X tags to shift to target different days, like this:
Is that correct? If so, here’s the formula I used in the Step Date field:
Justin, thanks for quick reply. This is very close to what I’m after but slightly different. In your example, this is what I’m after:
Step 1 Starts on Day 1 which is 1/1/2019, so does Step 42, 46, 47, 65, 89 & 98
At the end of Day 1 we didn’t finish Steps 65, 89 & 98. They will now be scheduled on Day 7. We also realize we need to swap Day 3 with Day 8.
Also to note, days are typically M-F but not exclusively. And there is the instance where the whole schedule would shift but in this instance I am fine with entering the new dates manually as the “Day” to “Date” doesn’t change often but the “Steps” on the “Day” change daily.
I might not have been clear in my example, but any step can start on any day. I put them in order just to be quick, but if you change the selection in the Step Day field, the Step Date gets recalculated accordingly. So in my first example above, if I’d set Step 5 to be Day 1, it would also be dated as 1/1/2019. This also covers the need to move or swap steps to different days. The Step Day choice drives all of that.
Accounting for global schedule changes is a matter of adding the new start date once to the Process Start Date field, then grabbing the little box on the corner and dragging that across all steps. However, if you’ve got more than a dozen or so steps, this would be cumbersome. In that case you might consider adding a Project table that lists each project separately, with a designated start date field. In the table listing all of the steps, add a Link field pointing to the Project table so you can target all steps to a given project, then the Process Start Date could be a lookup of the date for that project. Then if the whole project schedule moves, you change one date on the project table, and the whole schedule on the steps table is updated.
To account for weekdays, thankfully Airtable has the WORKDAY() option, so the Step Date formula would look like this:
I should also mention that the number of days each project will take will require some modification to the Step Date formula. My formula so far works fine for projects taking 7 days or less because I’m only grabbing the last digit from the Step Day field. If your project spans no more than 99 days, you could use this alternate option:
For Day 1 through Day 7, it’ll also capture the space in front of the number, but the formula will still work. It’s when the number becomes three digits long that the formula would need to be significantly altered.
