Find next entry date

Andre Arsenault

Hi all,

I'm currently trying to figure a formula that would find the next entered date for specific tools from my Rental Out column to populate the Rental In column. We have a system that tracks the movement of our tools and I'd like to find the next date entered by specific tools to track the duration the tool was on certain sites.

For example, row 7 Hammer, the Rental In would be 02/07/21 which corresponds to the next entered date for the hammer. Row 6 Ladder, Rental In would be 23/06/21, row 5 Rental In would be 29/06/21, etc.

Any ideas?

Paul Newcome

My initial thought would have been to use the MAX/COLLECT solution.

Since that isn't working, try this...

=MIN(COLLECT([Rental Out]:[Rental Out], [Rental Out]:[Rental Out], @cell > [Rental Out]@row, Tool:Tool, @cell = Tool@row))

If it still isn't working, make sure that both of the date columns are in fact set to date type columns.

Leibel S

@Andre Arsenault

Try the below setup.

Add the below 2 columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

Make your Rental In formula as follows:

=INDEX([Rental Out]:[Rental Out],MAX(COLLECT([ROW#]:[ROW#], [ROW#]:[ROW#], <[ROW#]@row, [Tool]:[Tool], [Tool]@row)))

Please note this assumes (based on your description) that all new lines have been added from the top.

Another possible option is:

=MAX(COLLECT([Rental Out]:[Rental Out], [Rental Out]:[Rental Out], <[Rental Out]@row))

Andre Arsenault

Unfortunately this didn't work, comes up as NO MATCH. To simplify what I'm trying to achieve, I need to find the next entered date for any specific item.

Leibel S

@Andre Arsenault

Which formula did you choose?

See below slightly revised for the second option:

=MAX(COLLECT([Rental Out]:[Rental Out], [Rental Out]:[Rental Out], <[Rental Out]@row, Tool:Tool, Tool@row))

Andre Arsenault

@Leibel S

I tried the first one, but the revised second one doesn't work as well. Comes up No Match. I had been trying something similar with no luck.

Andre Arsenault

Here is another example of what I'm trying to find. Rental In would be where the formula would be, colour coded with the data it would search and find which corresponds for the next entered date for that particular item.

Leibel S

@Andre Arsenault

Are both the Rental in and Rental Out fields a 'Date' Column type?

Paul Newcome

My initial thought would have been to use the MAX/COLLECT solution.

Since that isn't working, try this...

=MIN(COLLECT([Rental Out]:[Rental Out], [Rental Out]:[Rental Out], @cell > [Rental Out]@row, Tool:Tool, @cell = Tool@row))

If it still isn't working, make sure that both of the date columns are in fact set to date type columns.

Andre Arsenault

@Paul Newcome

Thank you so much, the MIN(COLLECT) worked perfectly.

Paul Newcome

Happy to help. 👍️

That second screenshot was what clued me in. For future reference... When posting questions to the Community, screenshots are always helpful, and (when possible) a screenshot with manually entered data that shows the desired outcome can really go a long way towards finding the most appropriate solution.