Drag a formula date to cover a year

androdriguez65476

Hi!

I'm creating a yearly calendar where the dates have a pattern (every date repeats 24 times), and I'm only including a work week (M - F).

Is there a faster way to fill it out until the end of the year without dragging the cell little by little? Smartsheet doesn't allow me to drag it more than ten rows at a time.

Jason Albrecht

Hi @androdriguez65476

The first couple of solutions that comes to my mind are:

• Build in excel, use the find/replace for the day number and paste into SS
• or Use a formula, copy / paste
  • Temporarily use the Hour field in the top row to type in the Day (e.g., 2 for day 2)
  • In the first date field type in the formula =DATE(2024, 9, Hour$1)
  • On this same cell, use keyboard shortcut Shift+DownArrow to highlight the total 24 cells
  • Press Ctrl+D, which should auto fill the cells.
  • Copy these cells, scroll down to the bottom of your list and press ctrl+shift+v and choose values
  • repeat until you need to change the Month / Year and use other helper cells with modified Date formula

Here's a couple of screenshot that will hopefully help:

I recognise this is still tedious, but a lot faster than 10 cells at a time.

Andrée Starå

Hi @androdriguez65476

I hope you're well and safe!

To add to Jason's excellent advice/answer.

I'd recommend creating a template with the year and starting date in the Sheet Summary Section so you can use them to generate column formulas for the structure. I'd then create the pattern and then drag it down/copy past, and if it makes it easier, you could use so-called helper columns with the year, month, and day.

Make sense?

Would that work/help?

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!