Find min and max values in Range

Question

I need to prepare the student attendance sheet from zoom meeting data file. My data is something like this

Name (Original Name)	roll no.	Join Time	Leave Time	Duration (Minutes)
002-Haseebuddin Shakir	002	22/01/2021 03:42:49 PM	22/01/2021 03:46:34 PM	4
002-Haseebuddin Shakir	002	22/01/2021 03:50:49 PM	22/01/2021 05:31:34 PM	100
003_Abdul Wasay	003	22/01/2021 03:53:51 PM	22/01/2021 04:31:30 PM	98
003_Abdul Wasay	003	22/01/2021 04:35:51 PM	22/01/2021 05:00:30 PM	60
003_Abdul Wasay	003	22/01/2021 05:01:51 PM	22/01/2021 05:31:30 PM	20
004_Ismail (04_Ismail)	004	22/01/2021 03:47:44 PM	22/01/2021 05:31:31 PM	104
005 saud	005	22/01/2021 03:51:07 PM	22/01/2021 05:31:43 PM	101
006-Akber (Akber-006)	006	22/01/2021 03:41:12 PM	22/01/2021 05:05:43 PM	85

I need help to find the 1st joined time, last left time and total of minutes. for understanding purpose used random figures.

I have tried thru pivot table but getting date 1900 whenever I use min/max and even tried aggregate function but I am not that perfect using aggregate.

Looking for output

Name	Roll no	Join time	Last leave time	total no. min's
002-Haseebuddin Shakir	002	22/01/2021 03:42:49 PM	22/01/2021 05:31:34 PM	104
003_Abdul Wasay	003	22/01/2021 03:53:51 PM	22/01/2021 05:00:30 PM	178

Answer 1

I propose a powerquery/powerpivot solution like this -

• remove auto filter
• select data
• from table on get&transform group on Data tab

it will look like this

• select name and rollnum both, rightclick group-by. add aggregations like the screenshot

• close and load the data back (it will automatically load to a new sheet in same book)

Note I think you'll these aggregations for each of the day for each of the roll number. It is therefore, advised that create a separate column of date before loading the data into powerquery window and thereafter groupby on name rollnum and date columns.