👋 Welcome! Introduce yourself and connect with your peers in Education to receive your industry badge.
Flagging a Date and Time Overlap
Answers
-
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
-
@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?
-
@Paul Newcome , I want only end date compared in all cells, if two or more end dates are equal we need to flag
-
@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 What exactly do you mean by it doesn't work? Is it not flagging duplicate dates or are you getting an error code?
-
@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.
-
@Muni Pavan Kumar Reddy Kona Sorry about that. Sometimes my fingers move a little faster than they should.
-
@Paul Newcome I saw a sheet you made for someone else creating a flag for date and time overlap. Can you share that again?
-
@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
-
@Paul Newcome You are such an asset to this community. Good man!
-
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.
-
@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)
-
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!
