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!
- 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.
- That depends on the definition of business day being a workday.
2 functions:
https://exceljet.net/formulas/date-is-workday
Workday function and Wordkay.intl function
These functions must be used in conjunction with datediff
or
Networkdays function and Networkdays.Intl function
https://exceljet.net/formulas/get-workdays-between-dates
Summary
To calculate the number of workdays between two dates, you can use the NETWORKDAYS function.
Hope this helps
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- 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.
