Continuous Date Function

Mindy Schneider

Hi! I use the daily sheet that was built by Smartsheet as a template for 2021. I want mine to keep going through 2022. The formula in the template is:

=$Date$1 - VALUE(YEARDAY($Date$1) - 366)

How can I keep coping this down to go through 2022? I have tried just the normal grab and pull down but that doesn't work.

Seems like something simple I could do but clearly I'm not that smart!

Bassam Khalil

Hi @Mindy Schneider 

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Mindy Schneider

Sure! It looks like this but the dates end 12/31/2021 and I want them to keep going through 2022. I would like to keep the formula copied all the way down through 2022.

Andrée Starå

Hi @Mindy Schneider

I hope you're well and safe!

Try something like this.

• Please copy everything from row 3 to row 366 and paste it where you want it.
• Use this formula in the first row of the pasted range, =DATE(2022, 1, 1) - VALUE(YEARDAY($Date$1) - 1) and then continue the pattern down all the copied rows.

Make sense?

Did that work/help?

Also, I developed something similar to a client a while back, so I'll take a look if I go any other tricks.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mindy Schneider

Thanks! Is there a way to continue the pattern without going in and modifying each line? I thought Smarsheet would pick up what I was trying to do by dragging down, but it just copies the same date down.

Mindy Schneider

When i put in the formula for the first four dates then try to drag them down it just copies 1-4.