Autopopulation of dates based on cells values

Options
Kavs
Kavs ✭✭✭✭
edited 04/06/22 in Formulas and Functions

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!

Best Answer

  • Julio S.
    Julio S. Moderator
    Answer ✓
    Options

    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

Answers

  • Julio S.
    Julio S. Moderator
    edited 04/06/22
    Options

    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
    Kavs ✭✭✭✭
    Options

    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!


  • Julio S.
    Julio S. Moderator
    Answer ✓
    Options

    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

  • Kavs
    Kavs ✭✭✭✭
    Options

    Thanks @Julio S. Appreciate your time and help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!