Auto Populate Children Dates based on the parent first day of the month


Good evening - I am looking to create an auto date that maxes based on the month (i.e. 28 days in February or 30 days in September) based on the parent row.

I originally had it by calendar year and organized by the Month as the parent, and that parent row has several values that are linked to other sheets, however, I was asked to make it a trailing 12 months based on a date.

First Step: Took the "Growth Plan Date" that is populated from another sheet and linked it here (Row 1).

Second Step: Row 3 auto-populates the first date of the month based on the Growth Plan Date, so we have a full month of data.

Third Step: I need to auto-populate the children of this month based on Row 3 Date (so row 4 would be the first day of the month, too) and then populate the rows that are listed below for the max number of days in that month.

Two Questions:

  1. Do I need to create a max of 31 rows for each month and build the formula to stop at the max days for the month and then have blank rows? If yes, can I create automation that deletes the rows if the date is blank or is it only clearing cells the only option?
  2. Does anyone have a recommended formula that populates the first day of the month and then the next day and max out for the total available days based on the month?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ah. Ok. It used to be that if you had a number in the DAY portion that was higher than the highest day in the month (Nov 31st for example) it would throw an error. Now it looks like they have switched it up to just move on down the line.

    Give this a go:

    =IF(MONTH(DATE(YEAR(PARENT()), MONTH(PARENT()), [Hidden Helper]@row)) = MONTH(PARENT()), DATE(YEAR(PARENT()), MONTH(PARENT()), [Hidden Helper]@row))

    Basically we grab the month from the calculation and say that if it matches the month in the parent row then go ahead and output the date. If it the calculation does not have the same month as the parent row then it will be left blank.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!