Find the next date in a list INCLUDING the same date

TianaJones

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:

Paul Newcome

Sorry about that. You're right. I got ahead of myself.

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))

Paul Newcome

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

TianaJones

Yes, the time of change would still be different

Paul Newcome

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))

TianaJones

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:

Paul Newcome

Sorry about that. You're right. I got ahead of myself.

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))

TianaJones

That works brilliantly! Thank you so very much!!

Paul Newcome

Happy to help. 👍️