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.
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?