Validate duplicate with 3 variables

Murilo Masson

Hi guys,

I have been working since days in one formula, what I call "cascate formula", to validate 4 variables to define if an event require attention or not. An event can only be validated, if there is NO other event assigned to the same trainer in the same time frame (start and end date).

So, I have 4 columns (start date, end date, event and assigned to). I created some phantom columns (columns to receive some values that will be hidden). So, I tried:

Phanthom1: =IF(COUNTIF([Start Date]:[Start Date], [Start Date]1) > 1, 1, 0), but then I do not know how to cascate in the best way.

I also tried: Phantom2 =IF(AND(COUNTIF(Event:Event, Event1) > 1, COUNTIF([Start Date]:[Start Date], [Start Date]1) > 1, COUNTIF([Assigned To]:[Assigned To], [Assigned To]1) > 1), 1, 0), but it also did not work.

Can some of the masters of smartsheet help me here?

Thanks for your comprehension and support

Best Regards

Murilo

Paul Newcome

To evaluate how many events are scheduled for the same trainer within the same time frame I personally would suggest this...

=IF(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)

This will check for any date overlap for that specific trainer and flag accordingly.

L_123

try using a countifs

=if(countifs(Event:Event, Event@row, [Start Date]:[Start Date], [Start Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row)> 1,1,0)

Paul Newcome

To evaluate how many events are scheduled for the same trainer within the same time frame I personally would suggest this...

=IF(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)

This will check for any date overlap for that specific trainer and flag accordingly.

Murilo Masson

Hi guys,

thank you very much.. that helped a lot.

@L@123: your formula did not entirely worked for the main objective: evaluate if there would be any duplicity in dates for the same trainer, what the formula from Paul did it very well.

Paul Newcome

Happy to help. 👍️