# Date Range Conflict - Vacation Request Sheet

• ✭✭✭✭✭✭

@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!!

• ✭✭✭✭✭✭

You and me both!

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭

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

• ✭✭✭✭

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)

• 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

• ✭✭✭✭✭✭
edited 02/21/20

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

SHEET

• ✭✭✭✭

@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! 🤴

• ✭✭✭✭✭✭

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

• ✭✭✭✭

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!

• ✭✭✭✭✭✭

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

What is the exact error you are getting?

• ✭✭✭✭✭✭

You are more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

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

• ✭✭✭✭✭✭

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?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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

• ✭✭✭✭

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