Flagging a Date and Time Overlap

Muni Pavan Kumar Reddy Kona

@Paul Newcome ,

I read this blog. It is very useful.

I do not want time overlap. For me only date overlap of particular column is important.

I am confused about the formulas. I have only 2 columns "task start date", "task end date". I want to trigger an overlap when 2 are more dates same on "task end date" colomn. Can you please help

Paul Newcome

@Muni Pavan Kumar Reddy Kona Are you only wanting to compare the end date column, or are you wanting to also incorporate the start date column?

Muni Pavan Kumar Reddy Kona

@Paul Newcome , I want only end date compared in all cells, if two or more end dates are equal we need to flag

Paul Newcome

@Muni Pavan Kumar Reddy Kona If you just want to flag any rows with equal end dates, it would look something like this...

=IF(COUNFITS([End Date]:[End Date], [End Date]@row) > 1, 1)

Muni Pavan Kumar Reddy Kona

@Paul Newcome ,

It did not work, Please find the attached screen shot.

Error.JPG

Paul Newcome

@Muni Pavan Kumar Reddy Kona What exactly do you mean by it doesn't work? Is it not flagging duplicate dates or are you getting an error code?

Muni Pavan Kumar Reddy Kona

@Paul Newcome , My apologies. It it working. Actual formula you gave is below.

=IF(COUNFITS([End Date]:[End Date], [End Date]@row) > 1, 1

But small typo =IF(COUNTIFS([End Date]:[End Date], [End Date]@row) > 1, 1)

I placed them as flag it perfectly work fine for me. Thanks for your help.

Paul Newcome

@Muni Pavan Kumar Reddy Kona Sorry about that. Sometimes my fingers move a little faster than they should.

TsmithFastSigns

@Paul Newcome I saw a sheet you made for someone else creating a flag for date and time overlap. Can you share that again?

Paul Newcome

@TsmithFastSigns Take a look at the below thread. There should be what you need in there somewhere. I think it is up to 9 pages now. Hahaha

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

TsmithFastSigns

@Paul Newcome You are such an asset to this community. Good man!

TsmithFastSigns

What am I doing wrong here, @Paul Newcome

=IF(COUNTIFS([VEHICLE #]:[VEHICLE #], [VEHICLE #]@row, DATE:DATE, DATE@row, FINISH:FINISH, @cell >= START@row, START:START, @cell <= FINISH@row > 1, 1)

I changed your [virtual license] to [vehicle #]. I'm trying to make a vehicle schedule that will notify of overlap of vehicle use.

I'm getting "INCORRECT ARGUMENT SET" as an error.

Paul Newcome

@TsmithFastSigns You just forgot to close out the COUNTIFS.

=IF(COUNTIFS([VEHICLE #]:[VEHICLE #], [VEHICLE #]@row, DATE:DATE, DATE@row, FINISH:FINISH, @cell >= START@row, START:START, @cell <= FINISH@row) > 1, 1)

trevormain

Hi @Paul Newcome I just saw your overlapping formula and its fantastic! if I need to look across multiple days for the overlapping, how would I tweak the formula below? I have the following columns: Start Date, End Date, Start Time, End Time, Start - Finish (these last 2, using your formulas as well)

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

Thanks! Hopefully you can help!