# Formula to calculate the Next [Future] date in a string of dates

Options
edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭
Options

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.

• Options

Genius! This is exactly what I was looking for!

Thank you so much.

• ✭✭✭✭✭✭
Options

Excellent! Happy to help.

• ✭✭✭
Options

I can't get the formula to work, is there a different tick if the due dates are all in one column?

Options

Can you post a screen capture of your sheet and detail what you want the formula to do? (But please block out sensitive data)

• ✭✭✭
edited 08/15/22
Options

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.

• ✭✭
Options

@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.

• ✭✭✭
Options

Thanks @Trevor Houghton, The dates are vertically structured, so unfortunately that doesn't work.

Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!