Number of Weeks Between First and Last of a List of Dates

[AnyaK]

Hello,

Please could I ask for some help?

I have a list of clients and the dates of their appointments. For each client, I would like to work out the number of weeks between their first appointment (earliest date) and last appointment (most recent date).

I would be really grateful for your help.

Many thanks,

AnyaK

Example for MrExcel 21102024.xlsx
	A	B
1	Client ID	Date of Appointment
2	Client 1	01/01/2024
3	Client 1	02/02/2024
4	Client 1	03/02/2024
5	Client 1	12/03/2024
6	Client 1	22/03/2024
7	Client 1	01/05/2024
8	Client 1	02/05/2024
9	Client 1	03/05/2024
10	Client 1	07/05/2024
11	Client 1	23/05/2024
12	Client 1	01/06/2024
13	Client 2	02/01/2024
14	Client 2	17/01/2024
15	Client 2	27/01/2024
16	Client 2	03/02/2024
17	Client 2	07/02/2024
18	Client 3	03/03/2024
19	Client 3	21/03/2024
Sheet1

 

[hagia_sofia]

Hello, if you already have access to the new GROUPBY function, then maybe something like:

=LET(
clients,A2:A19,
dates,B2:B19,
first,DROP(GROUPBY(clients,dates,MIN,,0),,1),
last,DROP(GROUPBY(clients,dates,MAX,,0),,1),
HSTACK(UNIQUE(SORT(clients)),DATEDIF(first,last,"D")/7))

 

[AnyaK]

Hello, if you already have access to the new GROUPBY function, then maybe something like:

=LET(
clients,A2:A19,
dates,B2:B19,
first,DROP(GROUPBY(clients,dates,MIN,,0),,1),
last,DROP(GROUPBY(clients,dates,MAX,,0),,1),
HSTACK(UNIQUE(SORT(clients)),DATEDIF(first,last,"D")/7))

Hi hagia_sofia

I really appreciate you taking the time to reply.

Unfortunately, I don't' have access to the GROUPBY function.

My apologies

 

[hagia_sofia]

Many thanks for the reply. Please test this then:

=LET(
clients,A2:A19,
dates,B2:B19,
u,UNIQUE(clients),
first,MINIFS(dates,clients,u),
last,MAXIFS(dates,clients,u),
HSTACK(u,DATEDIF(first,last,"D")/7))

 

[AnyaK]

Many thanks for the reply. Please test this then:

=LET(
clients,A2:A19,
dates,B2:B19,
u,UNIQUE(clients),
first,MINIFS(dates,clients,u),
last,MAXIFS(dates,clients,u),
HSTACK(u,DATEDIF(first,last,"D")/7))

Thanks again for your help. Unfortunately, I'm getting a #SPILL! error.

 

[hagia_sofia]

#SPILL! error means either that there are some values that are blocking the formula from spilling, or the formula is placed into an official Excel table - could you please insert the formula somewhere where there are blank cells within 2 columns to test it and provide the feedback if it calculates and it is how the output should look like?

 

[AnyaK]

#SPILL! error means either that there are some values that are blocking the formula from spilling, or the formula is placed into an official Excel table - could you please insert the formula somewhere where there are blank cells within 2 columns to test it and provide the feedback if it calculates and it is how the output should look like?

Thanks so much, it now works perfectly!

My apologies for my error in initially placing the formula in a table.

Thanks again.

 

[hagia_sofia]

Glad to hear that & no need to apologize.