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.
Re: check if date and time already entered
8am is before the start time?
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?
Re: check if date and time already entered
Hello,
What is the logic?
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
start within range and less than end and end outside range
start time outside and end inside range and greater than start
Don’t have an account yet? Register yourself now and be a part of our community!
