Combining sequential dates with criteria

1

I have a sync table that with the names and individual dates when employees are out of the office. However, the dates are all shown individually. For instance, if an employee takes a week off, the table ends up with seven separate lines, each representing a single date within that week. I’d like to combine anything sequential for the same employee. See the example below.

Screenshot 2023-08-24 at 10.16.49 AM
Screenshot 2023-08-24 at 10.16.49 AM634×1420 45.3 KB

I’m aiming to develop a formula for a third column that can determine the “end date” of an out-of-office period. This formula needs to address three distinct scenarios:

  • Non-sequential Days: When there are no consecutive days, the end date will be the same as the initial date.
  • First Date in a Series: If the date marks the beginning of a series of consecutive days off, the end date should be set as the maximum date within that series.
  • Subsequent Dates in a Series: For dates following the initial one in a series of consecutive days off, the end date should remain blank.

Once I’ve successfully implemented the formula for these scenarios, I’m planning to use buttons and automation to copy this information to a separate table. This second table contains manually inputted data such as holidays and events. The buttons will only copy rows that have an end date, which is why I’d like to leave anything that isn’t the first date blank.

Any guidance on devising a formula that addresses the three scenarios outlined above?

2

Your main wingman here is going to be Filter()

I’d recommend a checkbox column called “Is Last In Sequence” or whatever, with formula…

DatesTable
  .Filter(
    /* get just the rows for this employee */
    CurrentValue.Name = thisRow.Name
    /* get just the rows whose date is the day after this one */
    AND CurrentValue.Date() = thisRow.Date + Days(1)
  ).Count() = 0
  /* if there are zero rows matching the above criteria, we're the last date. */

Does that help get you started? Basically in your formulas you’re going to want to take the whole table and Filter it down (and then from there you might grab the Last() result, or Count() how many made it through the filter)

3

Thanks Nick, that pointed me in the right direction! Last() could give you the wrong date if the employee had another series of days off in the future (we want the “local” last, not the “global” last). I was able to get it working by filtering the column with your above formula, looking at only dates > the current value date, and then doing .sort().nth(1).

1 Like