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

Allison Horn

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?

Paul Newcome

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.

Paul Newcome

My suggestion would be to add 31 rows for each month, insert a helper column (that can be hidden after setup) with the number 1 - 31 in each of the sections, then using this column formula for the date:

=IFERROR(DATE(YEAR(PARENT()), MONTH(PARENT()), [Helper Column]@row), "")

Allison Horn

Thank you. Here is what I did since our Month changes based on another date. I found your formula in another post.

1. Added the # of days based on the date in that month (Date Column)
2. Added your formula above to the child of that month, however, it goes straight to the end of the month. What am I missing for it to start on the first day of the month and run through 31 days (if appropriate) by the helper column?

Paul Newcome

Do you have a column that has 1 - 31 manually entered? If not, I would insert that (and then hide it after setting everything up).

Then you can use

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

Allison Horn

Just added the manual entry for each month and tested the formula in the date column, but as you can see, it's still putting December 1 in November, and I would want to eliminate that row as it will be duplicated in December and shown in a report.

Paul Newcome

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.

Allison Horn

Thank you, you're a genius, and appreciate the troubleshooting! :)

Paul Newcome

Happy to help. 👍️