Drag a formula date to cover a year

Options

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.


Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    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.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!