Annual Calendar by Day Template

Tom Theado

Hi, We used the Annual Calendar by Day Template and it works great for the current year. However, we want to continue using the sheet in 2019 below the current year. When I copy the rows to below the current calendar, the formula is not easy to update. 

Row 373 holds the date the year is generated from and starting in the next row would be 01/01/19 and it continues down the column. The formula in the cell is =$Date$373 - VALUE(YEARDAY($Date$373) - 1). When I try to copy this formula down the column it does not automatically change 1 to 2 to 3, etc. 

Also I need to the $DATE$373 in both locations.

Is there any easy way to update this formula for the entire year?

Thank you!

Paul Newcome

Use the following:

 

=Date### + 1

"Date" being the column name that the date is in, and ### being the row number containing 31 Dec. 2018

 

Dragfll on down and it will take care of what you need.

Tom Theado

Paul - Thank you very much!!!!!!!

Paul Newcome

Happy to be of assistance. One thing I have found is that some of the formulas used in the templates are much more complex than necessary. Sometimes I think they build them that way to make it seem like their template is more irreplaceable than it really is. Haha.

Sue Hill

The newer version of this for 2020 has a flaw. Cannot use for 2021 without much time altering it.

The year 2020 was a leap year. When I changed the year to 2021 all the formatting for the first of the month moved down a row after starting March 1.

Solution is to revert the sheet back to 2020, delete 2/29/2020 row, save then change to 2021, and starting with 3/2--go day by day in the Date field and manually change the formula to 1 less...=$Date$1 - VALUE(YEARDAY($Date$1) - 61) changing to 60 ; for all 365 days. Sounds fun??

Is there a way to automate this change?

JewelsHuff

@Paul Newcome is there a way to skip if Sat or Sun? or a holiday?