Date Range Conflict - Vacation Request Sheet

Maricarmen Vargas

Hello,

I've seen quite a few questions for date range overlap, but couldn't find the solution. The closest I found was the one below, but it was referencing another sheet, not the same sheet I believe, and not sure a simple edit to that formula will make it work for me.

Form requests will populate new rows of all time off requests.

Before approving it, need a quick way to determine if there is date overlap with another request. See request for Employee X and Y.

Start and Finish are regular Date fields, just with changed formatting.

Thanks a lot!

https://community.smartsheet.com/discussion/51726/check-dates-against-a-range-of-dates-for-any-overlap

Alejandra

Hi @Maricarmen,

Does this formula work for you?

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

Paul Newcome

@Alejandra Couldn't this be simplified to...

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

?

Alejandra

Hi @Maricarmen,

Does this formula work for you?

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

Paul Newcome

@Alejandra Couldn't this be simplified to...

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

?

Alejandra

I suppose it could! I always get a little nervous posting formulas, because there's always a better and more efficient way of doing things, but I was hoping a formula guru would come along and teach me a thing or two... Thanks @Paul Newcome 😁

Maricarmen Vargas

Both worked for me!!

A million thanks!

Paul Newcome

@Maricarmen Happy to help! 👍️

@Alejandra Haha.

No worries. One of the things I look for when writing formulas are statements that have the same output because they can usually be combined with an OR.

When it comes to date overlap... I have been writing a ton of formulas for this every day for about a year and a half now building out a very detailed YTD breakdown of numerous stats for my department here at work. Being able to simplify that particular portion of the formula simply comes from literally tens of thousands of formulas referencing it to show yearly/monthly/weekly breakdowns based on a project's start and finish date. I can't even begin to count how many days I have spent beating my head against a wall trying to figure out an efficient way to do an overlap check. Haha

Paul Newcome

@Alejandra I almost forgot to mention...

@Andrée Starå is the guru 🧙 here. I'm just the mad scientist 👨‍🔬. HAHA!!

Alejandra

LOL! Thanks for the tip @Paul Newcome!

Andrée Starå

@Paul Newcome Haha!

https://media.giphy.com/media/KPdzGp8a20QbC/giphy.gif

Fletcher Boll

Hello! I am having a similar issue but the formulas above are giving me an #unpars:

=IF(COUNTIFS([end date]4:[end date]80,>= start date@row, [start date4]:[start date]80, <= end date@row) -1>0,1))

I am trying to flag if there is a workshop scheduled at the same time throughout the program...

Screen Shot 2020-02-19 at 1.14.06 PM.png

@Andrée Starå @Paul Newcome 🙏

Paul Newcome

You are missing square brackets around one of your column references, you have a row number insides of the square brackets, and you have an extra closing parenthesis on the end.

=IF(COUNTIFS([end date]4:[end date]80,>= [start date]@row, [start date]4:[start date]80, <= end date@row) -1>0,1)

Genevieve P.

Hi @Fletcher Boll

Quick note to add to Paul's edits, you will want to make sure that your formula is referencing the exact name of your columns... including capitals.

It looks like your "end date" column is actually called "End Date" - this will need to be referenced with capitals in the formula as well:

=IF(COUNTIFS([End Date]4:[End Date]80, >= [Start Date]@row, [Start Date]4:[Start Date]80, <= [End Date]@row) -1>0,1)

Here's some information on referencing columns that may help:

https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula

Cheers,

Genevieve

Paul Newcome

@Genevieve P The column names in formulas actually aren't case sensitive anymore. If the column name is Start Date, I can enter

[Start DATE]

[StArT DaTe]

[start date]

and any other case combination, and (as long as the spelling itself is correct and I am using the square brackets if needed), the formula will automatically update the case once you leave the cell.

I can't remember when that update was made (I didn't find out about it until a while after it was in place), but it sure was a huge lifesaver for me and my fat fingers. Haha.

EDIT:

I took a read through your link, and I found this little note:

"NOTE: If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula."

I thought this used to cause a #CIRCULAR REFERENCE error or something...?? Has that always been like that??

Genevieve P.

Just tested and 🤯

Thank you, Paul!

Paul Newcome

🤯👍️

Andrée Starå

Hi @Genevieve P & @Paul Newcome,

What?

"NOTE: If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula."

I thought this used to cause a #CIRCULAR REFERENCE error or something...?? Has that always been like that??

https://media.giphy.com/media/LpLd2NGvpaiys/giphy.gif

That has to be something new. Not sure when it changed because I was so used to not put the formula in the same column.

https://media.giphy.com/media/3o72F7YT6s0EMFI0Za/giphy.gif

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD