Need formula to find Minimum date and maximum date per each subject

[zeal7619]

Hello helpers,

Could you please let me know the formula to figure out the minimum date and max date per subject in the attached spreadsheet.

 

[ETAF]

what version of excel do you have
you could use
UNIQUE() to get a list of the subject numbers
then MINIFS() for min and MAXIFS() for max

which column is the date you want to find the min and max of

 

[Peter Bartholomew]

This follows @ETAF and uses 365 to build a separate table with each subject sorted and on a single row

= LET(
    distinctSubjects, SORT(UNIQUE(Table1[Subject Number])),
    earliestStart,    MINIFS(Table1[Start date], Table1[Subject Number], distinctSubjects),
    latestStop,       MAXIFS(Table1[Stop date],  Table1[Subject Number], distinctSubjects),
    HSTACK(distinctSubjects, earliestStart, latestStop)
  )

 

[zeal7619]

what version of excel do you have
you could use
UNIQUE() to get a list of the subject numbers
then MINIFS() for min and MAXIFS() for max

which column is the date you want to find the min and max of

I use off 365 sir. I would like to get the Minimum date from Column D (Start Date) and Max date from the Stop date column E.

 

[zeal7619]

This follows @ETAF and uses 365 to build a separate table with each subject sorted and on a single row

= LET(
    distinctSubjects, SORT(UNIQUE(Table1[Subject Number])),
    earliestStart,    MINIFS(Table1[Start date], Table1[Subject Number], distinctSubjects),
    latestStop,       MAXIFS(Table1[Stop date],  Table1[Subject Number], distinctSubjects),
    HSTACK(distinctSubjects, earliestStart, latestStop)
  )

Thank you

 

[Peter Bartholomew]

Since you use 3365, the attached shows the formula implemented.

 

[vletm]

zeal7619
Could You use basic Pivot-table, without any formulas?