CODEWITHSUNDEEP
excelexcel-formulaformula
Mr_Col
Mr_Col

Reputation: 15

Is it possible to group dates together and return a start and end date for that group?

I'm looking for a bit of help. I have a very long list of dates for when a piece of equipment has been booked in excel.

What I would like to do is to search the list for the piece of equipment (tool 1), then look at all the dates associated with it. If the dates are consequence then I would want to group these together and return the start and end date for the group (period). If there is a gap of 1 day or more, then it would look to the next group of consecutive dates, group these and return the start and end date for that period. Equally if it is just one day, then it returns the that date as the start and end date.... as an example I have this

Date Equipment
01/01/2019 Tool1
02/01/2019 Tool1
03/01/2019 Tool1
04/01/2019 Tool1
06/01/2019 Tool1
10/01/2019 Tool1
11/01/2019 Tool1
12/01/2019 Tool1
13/01/2019 Tool1
14/01/2019 Tool1
16/01/2019 Tool2
17/01/2019 Tool2

And what I want to end up with is this...

Equipment    Start Date    End Date
Tool 1       01/01/2019    04/01/2019
Tool 1       06/01/2019    06/01/2019
Tool 1       10/01/2019    14/01/2019
Tool 2       16/01/2019    17/01/2019

Equally, the piece of equipment will change through the list, and some dates may overlap as they can be booked at the same time - but I would list them separately - so a table,column for each individual piece of equipment.

Is this at all possible?? Please help, if you need me to explain further then please let me know.....

Upvotes: 0

Views: 475

Answers (2)

Black cat
Black cat

Reputation: 6132

With the use of 365 TOCOL and HSTACK functions a one cell workaround of it.
Next and next2 marks the sequential dates for start and stop respectively.
The HSTACK function first argument generates the respective Equipement value.
What is required that the title and the data are separated with an empty row for easier and cleaner formula.

=LET(next,IF((A41:A52=A40:A51+1)*(B41:B52=B40:B51),1/0,0),
start,IF(next=0,A41:A52,""),
next2,IF(A41:A52+1=A42:A53,1/0,0),
stop,IF((next2=1),"",A41:A52),
HSTACK(TOCOL(IF(ISNUMBER(start),B41:B52,1/0),2),TOCOL(start,2),TOCOL(stop,2))
)

Result sheet

Upvotes: 0

Tyler M
Tyler M

Reputation: 412

Keep in mind this is a very rough example and I'm sure some of the other members in the community can create a much more elegant solution, but here's one way to approach it:

Example solution

Column A - Dates, which MUST be organized in order for this solution to work. This is achievable with an Excel sort/filter.

Column B - Tools. In this solution I did not take into consideration Tool1 versus Tool2. However, maybe you will be able to separate out tools from each other in different sheets? Or perhaps you could come up with a clever way to take the tool into consideration.

Column C - Checks if the Column A date immediately follows the prior date, thus making it part of a time block.

Column D - Checks if the Column A date is the start date of a time block.

Column E - Checks if the Column A date is the end date of a time block.

Column F - Checks if the Column A date is both the start AND end of a time block. I.E. is the tool only rented for a single day?

Column G - Generates text associated with the start date.

Column H - Generates text associated with the end date.

Column I - Combines text from Column G and Column H to create a single column of time block information. Now we just need to remove the blank blocks (i.e. the white space) between date information.

If we remove the whitespace with a TEXTJOIN function, and then remove the CHAR(10)s that we don't want, we can get something like the following:

Example output

Again, this is a very rough example, but I hope it helps get you on the right track!

Upvotes: 1

Related Questions