Formula to copy all dates in a year for 120 rows?
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
-
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.
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!