Date column formula to start on a specific date and continue on after that.

Kaleb Riley

Hello,

So I have a helper sheet that tracks total number of entries for each month in each year. Right now I have a formula set up that checks the date above, and depending on if the current month is past that month, it will automatically fill in a new line with the current month (Due to date formatting, I have it set that every "Month" date shows the 1st of the month). I am using this function:

=IF(ISDATE([MM/DD/YYYY]9), IF(AND(MONTH(TODAY()) <> MONTH([MM/DD/YYYY]9), YEAR(TODAY()) <> YEAR([MM/DD/YYYY]9)), DATE(IF(MONTH([MM/DD/YYYY]9) = 12, YEAR([MM/DD/YYYY]9) + 1, YEAR([MM/DD/YYYY]9)), IF(MONTH([MM/DD/YYYY]9) = 12, 1, MONTH([MM/DD/YYYY]9) + 1), 1)))

where [MM/DD/YYYY] is the column that is using this formula. Row 9 is just the row above the row I copied the formula out of. My ISDATE() is to make sure it doesn't show #UNPARSEABLE for every subsequent row after the current month.

My Entries begin on 05/01/2021... My main goal is to turn this into a column formula while maintaining the autonomy. Currently, I will have to copy the formula down either a bunch of rows at a time, or every month, as smartsheet limits how many lines you can go down without data in them.

Does anybody have any ideas how I can turn this into a column formula, but something like "if there is no cell above then put 05/01/2021"?

Thank you in advance for your time and comments.

-Kaleb

Paul Newcome

Are you able to provide a screenshot for reference?

Kaleb Riley

So here is the portion of my helper sheet that the formula pertains to. Starting on the date I enter (05/01/21 row 1 column MM/DD/YYYY) the aforementioned column will automatically add the next month as each month comes. The columns to the left in white are just additional breakdowns of the full date as they are typically hidden. I just want to make the full date column become a column formula so that I do not have to continue copying down the formula every so often. I actually use this same method on different sheets when keeping tallys of data by month, so converting to column formula will be extremely helpful.

Paul Newcome

You are eventually going to need to adjust something if you are planning to add new rows for most solutions.

There is one solution that could fully automate it, but it requires a sheet summary field and a second sheet with a Copy Row automation set up on it. If you are ok with using the sheet summary field and a second sheet, then we can set it up to automatically add a new row on the 1st of each month.

Which option would you prefer?

NOTE: This second sheet that we are setting up the Copy Row automation can be used for all other instances where you need to do this. That means you wouldn't have to replicate the second sheet to be able to do this on other projects. You would just add in a new copy row automation to go to the new metrics sheet.

Kaleb Riley

@Paul Newcome,

So I currently have it automatically adding new months as they come. I will fully break down my formula for you

=IF(ISDATE([MM/DD/YYYY]9), IF(AND(MONTH(TODAY()) <> MONTH([MM/DD/YYYY]9), YEAR(TODAY()) <> YEAR([MM/DD/YYYY]9)), DATE(IF(MONTH([MM/DD/YYYY]9) = 12, YEAR([MM/DD/YYYY]9) + 1, YEAR([MM/DD/YYYY]9)), IF(MONTH([MM/DD/YYYY]9) = 12, 1, MONTH([MM/DD/YYYY]9) + 1), 1)))

So IF the above cell is a date, then IF today's month AND today's year is not equal to the above month and year, then if the month above = 12 make month 1, add 1 to the year above and set the day to 1. If those are not true, then set year equal to year above, month = month of above plus 1 and set the day to 1. This is automatically filling in the months as they come.

What I was wondering is if it is possible to make this a column formula, starting at a set date. Currently I need to copy the formula down, and manually enter the first date. If it is not possible, it is not a big problem since I can go several months ahead of time, but column formulas for this would be nice if possible.

Paul Newcome

Ok. So even having this set as a column formula, you would still need to manually create new rows. For example, if you go ahead and pre-fill 10 rows, it will not automatically add an 11th row when the time comes. You will still need to go several months ahead of time and be sure to check it regularly so that you can add new rows as needed.

To make it a column formula, you would have to still store the starting date somewhere. You can either hard code it directly into the formula using a DATE function, or you can use a cell or Sheet Summary field. For this example I will be referencing a Sheet Summary field.

In a text/number column you will need to manually enter the numbers one through however many months you want to go out.

1

2

3

4

5

6

7

so on and so forth.

Then the column formula to populate the dates would be something along these lines:

=DATE(YEAR([Start Date]#) + ROUNDDOWN((MONTH([Start Date]#) + [Duration (months)]@row) / 12, 0) + IF(IF(MOD(MONTH([Start Date]#) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]#) + [Duration (months)]@row, 12)) = 12, -1), IF(MOD(MONTH([Start Date]#) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]#) + [Duration (months)]@row, 12)), 1))

This will also account for going into a different year and moving across multiple years. My test data goes out to 36 months accurately. I didn't test beyond that.