Excel Dates

Resolved · Urgent Priority · Version 365

Thu 13 Oct 2022, 11:06 Delegate Lawrence said...

Lawrence has attended:
Excel Intermediate course

Excel Dates

I have a list of dates like this:

14/02/2022 15/02/2022
15/02/2022 16/02/2022
16/02/2022 17/02/2022
17/02/2022 18/02/2022
14/03/2022 15/03/2022
04/04/2022 05/04/2022
11/04/2022 12/04/2022
12/04/2022 13/04/2022
13/04/2022 14/04/2022
11/05/2022 12/05/2022
19/05/2022 20/05/2022

I am trying to collate the group of concurrent dates like this:

14/02/2022 18/02/2022
14/03/2022 15/03/2022
04/04/2022 05/04/2022
11/04/2022 14/04/2022
11/05/2022 12/05/2022
19/05/2022 20/05/2022

There's over 20000 rows so to automate this would be great.

Thu 13 Oct 2022, 12:52 Trainer Marius said...

RE: Excel Dates

Hello Lawrence,

Thank you for your question. Are the dates in your spreadsheet laid out in two columns as shown in your post, or all in the same column?

Kind regards
Marius Barnard
STL

Thu 13 Oct 2022, 13:02 Delegate Lawrence said...

RE: Excel Dates

Hi Marius, they are laid out as below:

Cell A Cell B Cell C
Name Start End
Person A 11/05/2022 11/05/2022
Person A 05/07/2022 05/07/2022
Person A 09/02/2022 10/02/2022
Person A 10/02/2022 11/02/2022
Person A 14/02/2022 15/02/2022
Person A 15/02/2022 16/02/2022

Fri 14 Oct 2022, 14:23 Trainer Marius said...

RE: Excel Dates

Hello Lawrence,

Thank you for your question. A method that works for me is using XLOOKUP.

Create a separate list of all people in the original Name column (only one of each). In the next column, write an XLOOKUP similar to this:

=XLOOKUP(E2,A:A,B:B)

E2 holds the name of a person in the new unique names column.
A:A is the original Names column
B:B is the Start column
This will return the first start date for each name.

In the next column, write an XLOOKUP similar to this:

=XLOOKUP(E2,A:A,C:C,,,-1)

This is similar to the first XLOOKUP, but it will return the final finish date for each person (the -1 at the end of the formula makes it search from the bottom up).

For this method to work, make sure all the names in the original Names column are sorted together with the dates from oldest to newest.

I hope this helps!

Kind regards
Marius Barnard
STL

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Sorting List Subtotals

If you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows.
IMPORTANT: If you do not hide the details rows before sorting a subtotalled list, your subtotals will be removed and all of the rows in your list will be reordered.

View all Excel hints and tips