Autopopulation of dates based on cells values

Hi,

We have students filling out a form that includes start date, end date, and number of weeks they will be rotating on particular day of the week. Is there anyway to autopopulate subsequent rows based on the start/end date with the date they will be rotating each week?

Example:

Start Date: 3/1/22 End Date: 3/29/22 # of Weeks: 4 Rotation Day: Tuesday

Need the following dates autopopulated in subsequent rows: 3/8/22, 3/15/22, 3/22/22

Thank you for your help!

Find more posts tagged with

Accepted answers

Julio S.

Hi @Kavs ,

Thanks for the screenshots and detailed explanation. There currently isn't a way to auto-insert a variable number of rows based on a selected value for a given field. When you have a moment, please let our Product team know about your feedback by filling in this form, here. Thank you!

If you find that the number of weeks inserted are recurrent in some way, you could potentially set pre-defined project templates where the number of rows has already been populated. Alternatively, you may also have a single template to download where there are more empty rows than the maximum of selectable weeks and then could remove the exceeding ones as needed.

I hope these ideas can be of help.

Cheers!

Julio

All comments

Julio S.

Hi @Kavs ,

From what you explain, it sounds like you would like to calculate the end dates for tasks starting on 3/8/22, 3/15/22 and 3/22/22 based on a given duration and return the number of weeks between start and end and the rotation weekday. You could accomplish this by enabling dependencies in your project and using the duration column to calculate the end dates based on the start date and the established duration.

If you'd like to calculate the number of weeks in between the start and end dates, you could use a column formula in the line of =ROUND(NETDAYS(Start@row, End@row) / 7)

If the rotation day accounts for the weekday the task starts or ends, the following formula should return the desired value, if you'd like to account for the end date instead of the start, you can adjust the formula to be END@row as needed:

=IF(WEEKDAY(Start@row) = 1, "Sunday", IF(WEEKDAY(Start@row) = 2, "Monday", IF(WEEKDAY(Start@row) = 3, "Tuesday", IF(WEEKDAY(Start@row) = 4, "Wednesday", IF(WEEKDAY(Start@row) = 5, "Thursday", IF(WEEKDAY(Start@row) = 6, "Friday", IF(WEEKDAY(Start@row) = 7, "Saturday"))))))))

Here's a list of the functions used for each formula:

If the suggestions above can't help you obtaining the desired results, please make sure to include captures of your project where any sensitive data has been hidden.

Cheers!

Julio

Kavs

Hi Julio,

Thank you so much for your response! I had tried enabling the dependencies but did not get the desired results. I've added screenshots to give you a better idea of what we are currently doing that we are trying to make easier. The student applies and states how many weeks they need to rotate (in the attached example it's 14). Then we manually enter 14 rows based on that row show it shows up on the calendar (second attachment). We were hoping there was a better way to do this. I appreciate your help!

2nd screen shot.JPG

1st screen shot.JPG

Julio S.

Hi @Kavs ,

I hope these ideas can be of help.

Cheers!

Julio

Kavs

Thanks @Julio S. Appreciate your time and help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.