Formula to calculate the Next [Future] date in a string of dates
Hello,
I'm looking for a way to find the next task due date. Ideally, I'll be able to look at a row with several date columns, and determine the next date. This would be the MIN Date that is Greater than Today's date.
I can easily get the MIN Date, However, many columns are in the past. I want the next [future] date after today.
This is an array in Excel:
{=MIN(IF(A2:J2>TODAY(),A2:J2))}
Thanks for any thoughts.
Comments
-
Try something along the lines of
=MIN(COLLECT([First Column Name]@row:[Last Column Name]@row, [First Column Name]@row:[Last Column Name]@row, @cell > TODAY()))
This will collect all dates that are greater than today and then display the minimum value collected.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Genius! This is exactly what I was looking for!
Thank you so much.
-
Excellent! Happy to help.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I can't get the formula to work, is there a different tick if the due dates are all in one column?
-
Hi @Tori Green
Can you post a screen capture of your sheet and detail what you want the formula to do? (But please block out sensitive data)
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
I'm trying to do a similar formula for a "go live date" column where i want the next go live date after today.
I must be missing something because this is the formula I'm using: =MIN(COLLECT({Go Live Date}, {Go Live Date}, @ cell >today()))
Any help would be awesome.
-
@Toby W think you need to incorporate a range, using [column name]@row:[column name2]@row. Using '[' rather than using '{'. It should collect the first date from a group of cells assuming your dates are horizontally structured in rows as shown in the example.
-
Thanks @Trevor Houghton, The dates are vertically structured, so unfortunately that doesn't work.
-
Hi @Toby W
It looks like your formula structure is correct, however I notice there's a space between your @ symbol and "cell". @cell would need to be kept all together as one string, like so:
=MIN(COLLECT({Go Live Date}, {Go Live Date}, @cell > TODAY()))
Let me know if that was the issue!
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
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!