How to make my due date column continuous and with a specific time range in between dates
I am trying to make the due date column so that it enters a date for every 22 days. I want this to be an "endless" column in that it goes all the way down without having to be manually entered. Is there a way to set the due date column to calculate this automatically and fill out all of the rows under it?
Best Answer
-
Hey @Alicia D
No formula needed! 😊
Add two dates in with your preferred day-difference:
Then click on the blue square in the bottom-right corner and drag-fill those dates down your column. The sheet will automatically pick up on your pattern and increase the dates by the same number of days between your first and second date:
Here's more information: Create a series of numbers or dates with drag-fill
Let me know if that makes sense and works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Alicia D
You can use drag-fill to repeat a pattern (such as a specific number of days between dates). You will need to manually drag down the dates, but if you go as far down as your sheet currently is, then you can drag-down more rows once in a while as it starts to fill up.
Here's more information: Create a series of numbers or dates with drag-fill
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. Do you know what formula I could use to achieve this when I do the drag and fill? For instance if I wanted a date that is every 22 days or every 6 months
-
Hey @Alicia D
No formula needed! 😊
Add two dates in with your preferred day-difference:
Then click on the blue square in the bottom-right corner and drag-fill those dates down your column. The sheet will automatically pick up on your pattern and increase the dates by the same number of days between your first and second date:
Here's more information: Create a series of numbers or dates with drag-fill
Let me know if that makes sense and works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. this worked, thank you!
-
@Genevieve P. do you know if there is a way I can make it exclude assigning the next date on a weekend? For instance it would automatically go to the next work day.
-
Hi @Alicia D
There isn't a way to have the drag-fill exclude a weekend, but you could have a column help highlight if one of the days falls on a weekend day, and then manually adjust those rows +1 or -1 depending on what you'd prefer.
Try something like this:
=IF(OR(WEEKDAY([Due Date]@row) = 1, WEEKDAY([Due Date]@row) = 7), "Weekend", "")
Then you can set conditional formatting for any rows where the Due Date is a weekend (or simply Filter to see those rows) so you can easily adjust them.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!