Date Range Conflict - Vacation Request Sheet

Paul Newcome

All you should have to do then is update your range from looking at the entire column to looking at the children of that parent row.

CHILDREN(Date2) instead of Date:Date

Andrée Starå

@Fletcher Boll

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Patrick Finn

Hello!

I'm relatively new to Smartsheet. I can get the following formula to work, but is there a way I can only have it "look at" children and ignore parent rows?

=IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", IF(COUNTIFS(Finish:Finish, >= Start@row, Start:Start, <= Finish@row) - 1 > 0, 1))

Paul Newcome

@Patrick Finn It would look something like this...

=IF(COUNT(CHILDREN(Start@row)) > 0, IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", IF(COUNTIFS(Finish:Finish, >= Start@row, Start:Start, <= Finish@row) - 1 > 0, 1)))

Patrick Finn

Hmmm, that's not working for me @Paul Newcome.

Maybe some more context on my end would help!

So when I have two different projects with overlapping timelines, I'd like them to be flagged (the children boxed in yellow). I don't mind if items in the same project (the children boxed in red/pink) are flagged or not, since that's not an issue per say and something we can look past.

=IF(COUNT(CHILDREN(Start@row)) > 0, IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", IF(COUNTIFS(Finish:Finish, >= Start@row, Start:Start, <= Finish@row) - 1 > 0, 1))) only causes the parents to get flagged and the original formula I posted flags everything because of the parent timelines.

Paul Newcome

@Patrick Finn Yeah. That's definitely different than my original understanding of what you needed.

Add in a column (you can hide it after setup to keep the sheet looking clean) that pulls the parent row data.

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, PARENT([Task Name]@row)

Then you could use something like this to flag overlapping rows within the same set of child rows...

=IF(AND(Start@row <> "", Finish@row <> "", COUNTIFS([Helper Column]:[Helper Column], [Helper Column]@row, Start:Start, @cell <= Finish@row, Finish:Finish, @cell >= Start@row) > 1), 1)

alcapps

I've seen several formulas related to this but haven't found exactly what i'm looking for. I have a sheet for vacation requests. I do not want more than one person to take vacation at the same time. So, I would like to flag any request that has the same dates as another on the sheet. For example if emp#1 has requested for 6/19/20-6/30/20 and emp#2 requests for any of those dates (i.e. 6/20/20) then both requests should flag.

I may want to limit the flag to the first overlapping request (last submitted).

I have a columns for name, submitted, start, end and conflict (flag).

Paul Newcome

@alcapps Are you able to provide a screenshot of your sheet that shows data and column names with sensitive/confidential date removed, blocked, and/or replaced with "dummy data"?

Susan Worstall

@Paul Newcome Hi Paul, I am working with alcapps on this issue. Here is a screenshot of our issue:

Paul Newcome

@Susan Worstall Are you wanting something for the Conflict column or the Duplicate Request column?

Susan Worstall

@Paul Newcome the conflict column. We want the second staff member that puts in a request for a date(s) that someone else has already put in for to be notified of the duplicate request. Thanks so much for your help!

Paul Newcome

Ok. Try something like this...

=IF(COUNTIFS([Start Date]$1:[Start Date]@row, @cell <= [End Date]@row, [End Date]$1:[End Date]@row, @cell >= [Start Date]@row, [Supervisor Approval]$1:[Supervisor Approval]@row, @cell <> "Denied") > 1, 1)

This is assuming that each new entry will be in the next lowest row.

Susan Worstall

Unfortunately, that didn't work. I am getting the #UNPARSEABLE error in the conflict column.

Paul Newcome

Double check that the column names are correct. If they are, can you copy/paste the formula you entered directly from the sheet?

Susan Worstall

No luck this time, either. Here's what I am getting:

We previously tried these formulas, but it was flagging (and notifying) the original submitter of the vacation request or we were getting an error msg:

=IF(AND($[Start Date]$1 < [End Date]@row, $[End Date]$1 >= [Start Date]@row), 1)

=IF(OR(ISBLANK(Start Date), ISBLANK(End Date)), "", IF(COUNTIFS(End Date:End Date, >= Start Date, Start Date:Start Date, <= End Date) - 1 > 0, 1))