Help

How to return the ealier date and the latest date from two separated columns that was groupped?

347 2
cancel
Showing results for 
Search instead for 
Did you mean: 
NLOIA
6 - Interface Innovator
6 - Interface Innovator

Hi !

     Coleagues, I have a project in AirTable in a single table with 174 rows and 11 (eleven) categories that I can group these rows.
     Each of these 174 tasks has its own "Start Date" and "End Date" column, with date format in it.
     I want to know the earllier "Start Date" and the latest "end Date" inside each of these modules, when filtered by them.
     (The objective is to show a timeline filtered by these "MODULES", using these fields with this formuila that identifies the earliest and the lates dates... I'm open to other approachs).
   I achieve the first step with these two formulas. They returns the date in each of these fields for its row:

1 - For the earliest "Start date" it is:
MIN( VALUE(DATETIME_FORMAT({Start date}, 'MM/DD/YYYY')) )

2 - For the latest "End date" it is:
MAX( VALUE(DATETIME_FORMAT({End date}, 'MM/DD/YYYY')) )

But !!...

These formulas, based in the columns (fields) "start date" and "end date",
returns the date of the row they are inserted.
e.g.1: If the "start date" of the row is 9/13/2023 it returns 9/13/2023.
e.g.2: If the end date of the row is 1/31/2024, this formula returns 1/31/2024.

It is not what I want.
I want this formula to bring the earliest date in the entire column called "Start Date" and that brings the latest date that is in the column (field) "End Date).

How to achieve it?

PS: The second step will be to get such earliest and lates dates from the 11 groupped modules I have.

PRINT OF THE TABLE:

NLOIA_0-1709587232926.png

 

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Ah, you can't do that I'm afraid, and will need to use linked fields and rollup fields for this purpose.  You can try:
1. Convert the single select field you're using to group into a linked field
  - This will create another table where each record is an option from the single select field
2. In the new table, create a rollup field to display the 'Start date' value from your main table, and use the formula 'MIN()'
3. In the new table, create a rollup field to display the 'End date' value from your main table, and use the formula 'MAX()'
4. In your main table, pull over the rollup fields you created in step 2 and 3

Should do what you need!

NLOIA
6 - Interface Innovator
6 - Interface Innovator

Hi!

     Thank you for the response  @TheTimeSavingCo .

      I will check your proposal as soon as possible and, since done, I'll let you know the result here.

Best regards!
@NLOIA 
Someone who learns.