Date Range Conflict - Vacation Request Sheet

Paul Newcome

@Andrée Starå

Same here! I got so used to having to put it in a different column, and I never saw any update or anything.

I plan on building out a test sheet to see what the limits are on this. I did a quick test earlier using a SUM function in one of the middle rows referencing an entire column, and it worked without hesitation!!

Andrée Starå

@Paul Newcome,

You and me both!

I also tested a little and so far it works for SUM, SUMIF and COUNTIF.

Genevieve P.

@Paul Newcome & @Andrée Starå

So much being discovered in this thread, I love it!

This will only work for one formula in that column, though - as soon as you have two formulas in the same column referencing that column you'll get #BLOCKED and yes, #CIRCULAR REFERENCE

Paul Newcome

@Genevieve P I figured that would be the case (but hadn't made it that far yet) since logically you're referencing a cell that contains a formula that references that cell. Good to know for sure though!

@Andrée Starå I think the first one I am going to test when I get a chance to is COLLECT.

Fletcher Boll

Thanks @Genevieve P for that info amount formula placement.

We changed our sheet- no longer need an end date. But we would like to add a time to the date and then flag if there is a duplicate in that program.

Any ideas are very welcome!

I tried this but it's not working =COUNTIFS([Start Date]4:[Start Date]80 = <1, 1) and =COUNTIF([Start Date]4:[Start Date]80 = <1, 1)

NEED TO ADD:

• times to either the Start Date column or a time column
• set formula to flag dups

@Andrée Starå @Paul Newcome Thank you all sooo much!

T

Paul Newcome

@Fletcher Boll I have some date/time overlap solutions that were built recently for others in the community. I will see if I can find the threads and give you some links to see if you are able to work with any of them.

EDIT TO INCLUDE LINKS:

SHEET

THREAD

Fletcher Boll

@Paul Newcome Hi Paul! Thank you for your help, the start and finish formulas are great. However, I am having trouble with the overlap. It is flagging all of the rows instead of the ones that are overlapped. I highlighted in yellow the rows that should be flagged.

I copied this formula but turned [virtual session] to [Workshop]

Thank you Paul! You da best! 🤴

Paul Newcome

Can you copy/paste the exact formulas directly out of the sheet? I also notice that you have 3 rows highlighted, but only two of them are overlapped (based on what is visible in your screenshot).

Fletcher Boll

Hi @Paul Newcome

Thank you so much for your help! This is what I was using:

=IF(COUNTIFS([Workshop]:[Workshop], [Workshop]@row, Date:Date, Date@row, Finish:Finish, @cell >= Start@row, Start:Start, @cell <= Finish@row) > 1, 1)

However, now it is giving me an #invalidate argument 🤷‍♀️ I can share the sheet with you if that will help! Thank you, thank you!

Paul Newcome

Double check that your date column is set to be an actual date type column.

What is the exact error you are getting?

Andrée Starå

@Fletcher Boll,

You are more than welcome!

Fletcher Boll

@Paul Newcome

Hi Paul!

I can confirm that the date is date type. I was able to get this to work and its great- we are fine with flagging based on duplicate dates only (no time or workshop).

ROW 6 formula =IF(COUNTIF(Date:Date, Date@row) > 1, 1)

However, if the cell is blank, it is flagging all rows and I can't seem to figure out ISBLANK

ROW 5 =IF(ISBLANK(Date:Date, Date@row, "cell is blank", 0, =IF(COUNTIF(Date:Date, Date@row > 1, 1))))

ROW 3 =IF(COUNTIF(Date:Date, Date@row) > 1, 1, IF(COUNTIF(ISBLANK(Date:Date, Date@row) > 1, 0)))

thanks guys! You have been very helpful! @Andrée Starå @Paul Newcome

Andrée Starå

@Fletcher Boll,

I'm always happy to help!

You were close. The ISBLANK function should check each row one by one.

ISBLANK

Try something like this.

=IF(ISBLANK(Date@row), 0, IF(COUNTIF(Date:Date, Date@row) > 1, 1))

Did it work?

Paul Newcome

@Fletcher Boll Andree is correct. The ISBLANK function in this case should be looking at a cell reference instead of a range.

NOTE: Andree also adjusted a couple of syntax issues as well such as the = before the second IF and moving some of the closing parenthesis around.

Fletcher Boll

@Andrée Starå @Paula Cosentino it's close, but we had =MIN(CHILDREN() to track the workshop program dates as a whole. So it's reading that roll up at a duplicate date.

I may just remove the roll up for now- coming back to this when we have more time.