Extract date-ranges from a list of dates

Showcase Tips and Hacks
1

The client has a list of dates when people are available for tasks.
They want to see this compressed down into a simple list of date-ranges when they are available.

Rather than do it in one big-assed formula, I have broken it down into a series of simple formulas.
For convenience, I am just using the day numbers here. But the formulas work for coda date objects as well (since they are basically integer day counts as well).

Let me know if there are any questions - happy to explain it further if needed

Max

6 Likes
2

So, by popular demand, here is a breakdown of the formulas used above
(anything after the // is a comment and should not be included in the actual formula)

gaps // this generates a list of the gaps in the ranges

Sequence(2,daysList.Count()).FormulaMap(      // each daysList[2..end]
  CurrentValue.WithName(I,                    // using I as the index
    SwitchIf(                                 // check for a gap >1 day
      daysList.Nth(I)-daysList.Nth(I-1)>1,    // between dates I & I+1 
      List(daysList.Nth(I-1),daysList.Nth(I)) // if gap, return both dates
    )                                         // otherwise returns empty item
  )
).Filter(CurrentValue)                        // removes all the empty items


ranges // this generates a list of pairs of dates representing the start & end of each range

ListCombine(        // we will build a flat list of dates
  daysList.First(), // starting with the first date of the range
  gaps,             // then the list of gap dates
  daysList.Last()   // finally the last date of the range
)

rangeText // this joins each pair of dates with a hyphen giving the list of ranges as texts

Sequence(1,ranges.Count()-1,2).FormulaMap( // taking every 2 items in turn
  CurrentValue.WithName(D,                 // using D as the index
    Concatenate(
      ranges.Nth(D),'-',ranges.Nth(D+1)   // join D and D+1 dates with a hyphen
    )
  )
)
8 Likes
3

Coda community is blessed to have someone like you @Agile_Dynamics. Thanks for sharing this!

5 Likes
4

Now, if we could just store these as reusable in-doc formulas to be called with some variables. I was going to use packs for that, but I don’t like the the way they are executed in pack, making a round-trip to a (US based) server…
CFL is so much more friendly for building your custom formulas, I hope Coda will consider making page (only accessible to owners or makers) for storing these type of formulas, and perhaps a few defaults.

3 Likes
5

Indeed a wonderful contribution dear @Agile_Dynamics .

I took the freedom to create the inverse of your list, thus following the same logic to see the still available ranges (or dates since they follow a comparable logic).

I defined 4 steps. Below how it goes. I started with creation of a list of missing numbers and named it step 01. If you are new, the function Max() is not related to ‘Max’ :wink:

Sequence(1,daysText.Split(",").Max()).Filter(CurrentValue.Contains(daysText.Split(",")).Not())

image
image2168×881 121 KB

In step02 I make use of step01 and as you can see I changed two things. I replaced the SwitchIf for an If statement. However this is not a very good choice, the SwitchIf permits you to continue without expressing the case for if not. When you use an If statement, you need to tell what happens in case the condition is not met. In this case nothing so " ". This works, but is not very smart.

The second difference is the use of Concatenate() instead of the + , I Both work fine, it is mainly a matter of preference and habit I guess.

Sequence(2,Step01.Count()).FormulaMap(
  CurrentValue.WithName(I,
    If(Step01.Nth(I)-Step01.Nth(I-1)>1,
      Concatenate(Step01.Nth(I-1),"-",Step01.Nth(I)),""
    )
  )
).Filter(CurrentValue)

image
image1646×813 68.1 KB

I continue with the SwitchIf below, it is the better choice:

ListCombine(
  Step01.First(),
  step02.FormulaMap(
    CurrentValue.SwitchIf(
      CurrentValue.Split('-'))      
  ),Step01.Last())

image
image1508×711 55.3 KB

And we finish with the last step I shortened a bit by not using WithName(), this is a perso pref in this context, both work fine.

Sequence(1, step03.Count() - 1)
  .FormulaMap(
    step03.Nth(CurrentValue) + '-' +
      step03.Nth(CurrentValue + 1)
  )

image
image918×621 45.7 KB

What a wonderful exercise with a method that can be applied in so many docs, Merci @Agile_Dynamics

6 Likes
6

I have modified the example doc to show REAL DATES for REAL PEOPLE, so it is easier to apply to real world cases. (Well, ok, the people are fictional.)

The DATES table contains a record for each available date for each person. Which is how my client had their data organised.

The DateRanges table then reduces that to a list of available date-ranges for each person using the formulas explained above.

The only difference is that i have added .BulletList() to the end to format the output better.

Max

2 Likes
7

looks nice @Agile_Dynamics
did you consider to strip the double values in case it concerns a single date?

image
image518×688 41.2 KB

Cheers!

8

well…

turns out client expresses even single-day availability as D1…D1

however…

i have modified the formula to report a single-day availability is just a single date
as it makes more sense in general

so i changed the final formula for the Available column as follows…

Sequence(1,thisRow.Ranges.Count()-1,2).FormulaMap(       // loop over every PAIR of dates in Ranges
  CurrentValue.WithName(D,                               // indexing with D
    Concatenate(
      thisRow.Ranges.Nth(D),				             // show the 1st date
      SwitchIf(
        thisRow.Ranges.Nth(D)!=thisRow.Ranges.Nth(D+1),  // if 1st date & 2nd date are different
        Concatenate('...',thisRow.Ranges.Nth(D+1))       // show ... 2nd date
      )
    )
  )
).BulletedList()                                         // present the resulting list as bullets

thanks @Christiaan_Huizer for pointing out this improvement

as you pointed out, i tend to use SwitchIf() instead of If()
whenever i only want to specify a result for the TRUE condition
because SwitchIf() allows me to leave out the FALSE action
whereas the If() requires me to specify the FALSE action as well, even if its ‘do nothing’

max

3 Likes