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

jmhoward

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

Andrée Starå

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!

Andrée Starå

@jmhoward

jmhoward

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

https://community.smartsheet.com/discussion/comment/408980#Comment_408980

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.