Formula to copy all dates in a year for 120 rows?

Options

Hi all,

I need to add a formula to a sheet that will create 120 rows for the same date for all days in 2024. For example, 1/1/24 will have 120 rows, then 1/2/24 will have 120 rows and so on.

Is this possible?

Thanks!

Jackie

Answers

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

    Hi @jmhoward

    I hope you're well and safe!

    Can you elaborate on the use case? Also, you'll hit the sheet row limits, so you would need three sheets for the whole year. Would it still work?

    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.

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

    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.

  • jmhoward
    jmhoward ✭✭✭✭✭
    Options

    Thank you @Andrée Starå! My project is a different take on a master time sheet. In the sheet we have projects and sub-projects, Because the director did not want users selecting items from a drop-down menu, I instead have to create a row for each possibility for each date and then create filters and reports for each person. (Not ideal, I know.)

    After adjusting some options, I now have 78 rows per date. I see this is still too many. I supposed I could do one sheet per quarter to work around this. But I would still need to fill in the date for 78 rows per date.

    Here is the example of the 2023 sheet that the director approved. (We didn't exceed rows because we only did Q4 2023.)

    Many thanks for any advice you can provide.

    Jackie

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    The best way for you to do this (With different quarters) and without having to do groundwork each time is to set up a template for this. Create a summary field, call it startdate and then you just need to change 1 date to populate all dates.

    Row1-120, formula is =[startdate]#

    Rows121-240, formula is =[startdate]#+1

    ETC...

    If that is tedious, you can also create a helper column, call it rownumber, going 1, 2, 3, etc and use 1 formula for all dates.

    =[startdate]#+ROUNDDOWN(([rownumber]@row-1)/120)

    And that will give you 120 of each date.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!