 # check if date and time already entered

• hi guys,

Can some help me update my formula, the code works fine if the time start already in the list. however if time entered in having different start time but already within the time period entered it is not showing as duplicate.

basically, I want the file show me if the time entered is already something which has been accounted for?

in example, this show all as duplicate, but G4 is not showing as duplicate.

## Files

• Re: check if date and time already entered

yes but I want to say its duplicate as long the time in between already entered data? how can I update this formula?

• Re: check if date and time already entered

mabye with your formula if either the start or the end time falls within the time
[COLOR="#0000FF"]=IF(B7="","",IF(OR(SUMPRODUCT(((B7+C7)>=Start)*((B7+C7)<=End )),SUMPRODUCT(((B7+D7)>=Start)*((B7+D7)<=End ))),"Duplicate",""))[/COLOR]

• Re: check if date and time already entered

thanks Pike, this works but comes with different issue, where is start time is from 8am to 8:30am then this is not duplicate, but the formula says its duplicate

• Re: check if date and time already entered

i have updated the formula, and its working now:

=IF(B8="","",IF(OR(SUMPRODUCT(((B8+C8)>=Start)*((B8+C8)<=End )),SUMPRODUCT(((B8+D8)>Start)*((B8+D8)<End ))),"Duplicate",""))

Thanks a lot pike

• Re: check if date and time already entered

Sorry Pike, this has not worked as I supposed it would be. Please see attached file. Can you please advice?

## Files

• Re: check if date and time already entered

maybe
[COLOR="#0000FF"]=IF(B10="","",IF(OR(SUMPRODUCT(--((B10+C10)>=Start)*--((B10+C10)<End)),SUMPRODUCT(--((B10+D10)>Start)*--((B10+D10)<=End)),SUMPRODUCT(--((B10+C10)<=Start)*--((B10+D10)>Start))),"Duplicate",""))[/COLOR]
but I may missed the another logic step

• Re: check if date and time already entered

or as an array formula
[COLOR="#0000FF"]=IF(B10="","",IF(OR(--((B10+C10)>=Start)*--((B10+C10)<End),--((B10+D10)>Start)*--((B10+D10)<=End),--((B10+C10)<=Start)*--((B10+D10)>Start)),"Duplicate",""))[/COLOR]
entered with control+ Shift + Enter keys

• Re: check if date and time already entered

Thanks Pike, the array formula did the trick.

• Re: check if date and time already entered

arr good
think there are four logic parameters
both times within range
both time out side range