Date column formula to start on a specific date and continue on after that.
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.
Help Article Resources
Check out the Formula Handbook template!