I am looking for a way to have my template define a year and then be able to have it update rows that have the day in the month using that year. FYI: template says in the row something like 1/15/"Year" so that I when I rollover to the new year I don't need to update each individual row.
I fear there is more to your question that your post reveals...
At its simplest level, assuming your year is in cell A1 with the value '2026', you can just construct a string value like:
="1/1/"&$A$1
which will return the string:
"1/1/2026"
This is a text object, though, so may have issues if you're sorting the table or performing date-based calculations on the result. If you need it as a date value, then use the DATE() function to build your date. Date takes three parameters, one for the year, the month, and the day:
=DATE($A$1,1,1)
=> 1/1/2026
I fear there is more to your question that your post reveals...
At its simplest level, assuming your year is in cell A1 with the value '2026', you can just construct a string value like:
="1/1/"&$A$1
which will return the string:
"1/1/2026"
This is a text object, though, so may have issues if you're sorting the table or performing date-based calculations on the result. If you need it as a date value, then use the DATE() function to build your date. Date takes three parameters, one for the year, the month, and the day:
=DATE($A$1,1,1)
=> 1/1/2026
Another thought that just came to me was using SEQUENCE() to build the list of dates.
For the first date cell, put:
=SEQUENCE(1,365,DATE($A$1,1,1),1)
This will build an array of 365 dates starting at 1/1 of the specified year. You can add a leap year wrapper to this to catch leap years, like:
IF(MOD($A$1,4)=0,SEQUENCE(366,1,DATE($A$1,1,1),1),SEQUENCE(365,1,DATE($A$1,1,1),1))
One other caveat to consider is leap years.
If you're filling the table with sequential month numbers and day numbers, you may need to account for leap years.
For the cell that would be February 29th, you can add a simple check like:
=IF(MOD($A$1,4)≠0,"",DATE($A$1,2,29))
which checks if the year is divisible by 4 (*) and either outputs a blank field or the calculated date, accordingly.
* yes, I know that the turn of the century is not a leap year, but we have ≈70 years to worry about that, and if this spreadsheet is still in use by that time, I'll happily update the formula to account for that ;)
Again I'm loving the array functions. Here is another way to handle leap years
=FILTER(SEQUENCE(366,1,DATE(A$1,1,1),1),SEQUENCE(366,1,DATE(A$1,1,1),1)≤DATE(A$1,12,31))
Thank you, figured out the DATE function per your reply. I am creating payment schedules. The difference being that my old way of doing it I used fixed dates. The advantage of that approach is you can start a sequence say, 1/1/2025, 2/1/2025 then you can select the two cells and then auto fill the next cells. Numbers will create the rest of the sequence (3/1/2025, 4/1/2025 , etc). That is something I tried to do within the date function but it didn’t appear to work. But now that I have the tables built, next year I can just change the year on the summary page and the rest of the cells will update so it’ll be less work than having to touch each table.
Nice use of FILTER() to catch the leap year.
Auto-update year in template dates?
