# Find the next date in a list INCLUDING the same date

Options

I need some help, please! I've been all over discussions and tried everything I can think of but I can't get this to work.

I have a table that tracks status change dates for our projects. The date and time is recorded automatically each time the status changes. I want to show how long each status takes during the process. I am trying to get it to recognize the next date in the process for each project so I can then do the math for how long it was until that date. *of note, due to various factors the dates are not necessarily in order on the table, and all the projects are mixed together, so the formula has to find the next date for that specific project number.

Sometimes the status changes happens on the same day, and I want the formula to register that date and count it as '0 days' between those two statuses. This is the part I'm struggling with!

With all the Min/Collect formulas I've seen and tried it either:

1. returns the next date that isn't the same day and skips over instances where the status changed the same day which makes all the timelines downstream incorrect

OR

2. Always returns itself since it is the next date if you don't exclude same dates

If I were coding this I'd put it in a recordset/array by project number, have it sort by date/time, then run through the list and do calculations but I have no idea how to translate this to Smartsheet!

Here's a shot of my table - for simplicity I am only showing one project:

• ✭✭✭✭✭✭
Options

New helper column formula:

=VALUE(YEAR(Date@row) + "" + RIGHT("0" + MONTH(Date@row), 2) + "" + RIGHT("0" + DAY(Date@row), 2) + "" +

SUBSTITUTE([Time of Change]@row, ":", "."))

New Min/Collect:

=MIN(COLLECT(Date:Date, [Helper Column]:[Helper Column], @cell > [Helper Column]@row, [CCT #]:[CCT #], @cell = [CCT #]@row))

• ✭✭✭✭✭✭
Options

Even if it is on the same date, I assume the time of change would still be different?

• Options

Yes, the time of change would still be different

• ✭✭✭✭✭✭
Options

In that case I would suggest using a helper column to convert the time into a usable number

=VALUE(SUBSTITUTE([Time of Change]@row, ":", "."))

Then you can use a MIN/COLLECT combo along the lines of

=MIN(COLLECT(Date:Date, Date:Date, @cell >= Date@row, [TIme Helper]:[Time Helper], @cell > [Time Helper]@row))

• Options

Hi Paul,

That almost worked! However, when the next status update is on a different day with an earlier time than the previous date, it doesn't register with that equation:

• ✭✭✭✭✭✭
Options

New helper column formula:

=VALUE(YEAR(Date@row) + "" + RIGHT("0" + MONTH(Date@row), 2) + "" + RIGHT("0" + DAY(Date@row), 2) + "" +

SUBSTITUTE([Time of Change]@row, ":", "."))

New Min/Collect:

=MIN(COLLECT(Date:Date, [Helper Column]:[Helper Column], @cell > [Helper Column]@row, [CCT #]:[CCT #], @cell = [CCT #]@row))

• Options

That works brilliantly! Thank you so very much!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!