Forum Discussion
Auto-populate dates
Hello!
I need assistance with automating updates for the remaining dates in the 'Due date' column (Column C). Currently, we use a DateDif formula in Column B to determine the T-weeks schedule, which influences the milestones listed in Column F. For instance, if I modify the date of a milestone like the strategy review from May 7th to May 12th, I want the subsequent due dates to adjust accordingly based on the T-weeks schedule.
Thank you!
- To provide the best assistance, I'd like to understand your current setup a bit better. Could you please provide more details about:
The structure of your spreadsheet, including the columns you're using and any formulas you're currently employing.
The specific behavior you want when a milestone date changes. For example, do you want all subsequent due dates to shift proportionally, or do you have a specific rule for how they should be adjusted?
Any constraints or special cases we need to consider, such as holidays or non-standard working hours.Hi there! The only formula I am currently using is DATEDIF in column B. Yes, would like holidays to be considered and the business days only (M-F). If I change May 7th to say May 12th then I would like all subsequent dates in column C to update accordingly.
One way to do this is by using formulas in Column C that reference the milestone dates in Column F and the T-weeks schedule in Column B. For example, if the strategy review milestone is in cell F2 and the T-weeks schedule is in cell B2, you could use a formula like this in cell C2:
=F2 + (B2 * 7)
This formula adds the number of weeks specified in the T-weeks schedule to the milestone date, effectively updating the due date accordingly. Then, you can drag this formula down for all the remaining milestones in Column F, and the due dates in Column C will adjust automatically whenever you change a milestone date or the T-weeks schedule. Read More- aellmanbrown
Your screenshot does not show the row numbers.
spreadsheet formulas work through triangulation, without knowing the row numbers everyone will have to guess your screenshot starts at row 2
with the assumption that your starting at row 2 can you explain to everyone why the datedif function is referencing a start date in C7 and end date in A7 what are the values in those cells so the people on here can make some sense and figure out how the logic works so everyone on here can maybe suggest a better configuration to a bulletproof solution for you.Hi there! The only formula I am currently using is DATEDIF in column B. Yes, would like holidays to be considered and the business days only (M-F). If I change May 7th to say May 12th then I would like all subsequent dates in column C to update accordingly.
- In order for that to happen, then the values in column A should be calculated in order for the subsequent values to dynamically change up to the next constant value in column C, assuming the subsequent values in column C after May 7 are also calculated dates (its complicated)
I would guess that you might need a calendar table to base your event date column to dynamically adjust on the basis of the entry in the Due date in column C so that when you enter a constant date in column C, the event date will adjust accordingly by calculating the dates in column A to move relative to the constant value in column C on the previous row. (I'm assuming since I do not know the algorithm of the logic on your table)
You would have to explain the movement of any dates on the current row as well as the next row in order for the people on here to catch on to the algorithm date movement.
for example if you changed May 7 to May 12, will the date in column A same row change? (I assume not since your narrative is slanting to move forward hence I can safely assume that the date in column A next row would change?
