Create range of dates for OOO

Karen Arntz

Hi,

I have a list of data like the following…

RowID	Date	Name	Status
2	5/19/2025	Tom- OOO	OFF
3	5/20/2025	Tom- OOO	OFF
4	5/21/2025	Tom- OOO	OFF
5	5/22/2025	Tom- OOO	OFF
6	5/23/2025	Tom- OOO	OFF
7	5/26/2025	Tom- OOO	OFF
8	5/27/2025	Tom- OOO	OFF
9	5/28/2025	Tom- OOO	OFF
10	5/29/2025	Tom- OOO	OFF
11	5/30/2025	Tom- OOO	OFF
12	7/4/2025	Tom- OOO	OFF
13	9/1/2025	Tom- OOO	OFF
14	11/27/2025	Tom- OOO	OFF
15	11/28/2025	Tom- OOO	OFF
16	12/25/2025	Tom- OOO	OFF
17	5/26/2025	Jane - OOO	OFF
18	9/1/2025	Jane - OOO	OFF
19	12/25/2025	Jane - OOO	OFF
20	5/7/2025	Bob - OOO	OFF
21	5/8/2025	Bob - OOO	OFF
22	5/9/2025	Bob - OOO	OFF
23	5/26/2025	Bob - OOO	OFF
24	7/4/2025	Bob - OOO	OFF

I am trying to create a report that will provide a list of ranges the employee is OOO. It will look like this…

Name	Range
Tom - OOO	5/29/25 - 5/30/25
Tom - OOO	7/4/25 - 7/4/25
Tom - OOO	11/27/25 - 11/28/25
Tom - OOO	12/25/25 - 12/25/25
Jane - OOO	5/26/25 - 5/26/25
Jane - OOO	9/1/25 - 5/1/25
Bob - OOO	5/7/25 - 5/9/25
Bob - OOO	5/26/25 - 5/26/25
Bob - OOO	7/4/25 - 7/4/25

I tried using Min & Max, but it does not take into consideration when coming back into the office and then out again from the office. Any assistance will be appreciated

SSFeatures

Hi @Karen Arntz, I don't think there's really a good way to do what you're trying to do in Smartsheet. I can't think of a formula that can group together each individual segment of OOO dates.

Here's an alternative that won't work perfectly, but is better than nothing.

• Create a helper column named "Month and Year".
• Add a column formula to this column as

=MONTH(Date@row) + " " + YEAR(Date@row)

Then you can create a Report to filter out any rows that do not have "Status = OFF". Then you can group by Name and group by Month and Year, so at least all the person's out of office dates will be right next to each other.

Alternatively, if you have someone on your team comfortable with the Smartsheet API, then you could process the OOO dates outside of Smartsheet and then reinsert them into Smartsheet correctly.