validation formula to check if DateTime of new item is within DateTime of existing item

%3CLINGO-SUB%20id%3D%22lingo-sub-1767173%22%20slang%3D%22en-US%22%3Evalidation%20formula%20to%20check%20if%20DateTime%20of%20new%20item%20is%20within%20DateTime%20of%20existing%20item%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1767173%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20spent%20an%20exceptionally%20long%20time%20looking%20for%20a%20way%20to%20do%20this%20and%20can't%20find%20one%20(not%20one%20that%20I%20can%20use%2Funderstand%20anyway!).%20What%20I%20want%20to%20do%20is%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20a%20SharePoint%20List%20I%20have%203%20DateTime%20columns%20(because%20there%20is%20no%20way%20to%20have%20a%20single%20column%20that%20allows%20a%20user%20to%20enter%20date%20and%20'between%20times'%20i.e.%207-Oct-20%203%3A00PM%20-%204%3A00PM)%20and%20an%20'ID'%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20stop%20users%20from%20creating%20a%20new%20item%20if%20the%20DateTime%20columns%20in%20the%20new%20item%20fall%20within%20the%26nbsp%3B%20period%20of%20the%20existing%20DateTime%20columns%2C%20if%20the%20'overlapping'%20DateTime%20columns%20have%20the%20same%20item%20ID.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEXAMPLE%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22150%22%3E%3CP%3E%3CSTRONG%3EID%20Column%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E%3CSTRONG%3EDate%201%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E%3CSTRONG%3EDate%20%26amp%3B%20Time%202%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E%3CSTRONG%3EDate%20%26amp%3B%20Time%203%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22150%22%3E%3CP%3E5555%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E5-Oct-20%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E5-Oct-20%204%3A00PM%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E5-Oct-20%208%3A00PM%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22150%22%3E%3CP%3E7777%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E5-Oct-20%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E5-Oct-20%205%3A00PM%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E5-Oct-20%206%3A30PM%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22150%22%3E%3CP%3E5555%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E5-Oct-20%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E5-Oct-20%207%3A00PM%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22150%22%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E5-Oct-20%208%3A00PM%3C%2FFONT%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20above%20example%2C%20I%20want%20to%20stop%20the%20user%20from%20creating%20that%20item%20(and%20have%20an%20error%20message%20or%20similar%20come%20up%20to%20say%20%22time%20already%20exists%22%20or%20something%20to%20that%20effect)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20how%20I%20could%20achieve%20this%20(either%20by%20using%20a%20'Flow'%20or%20column%20validation)%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20%3CU%3E%3CEM%3E%3CSTRONG%3Egreatly%3C%2FSTRONG%3E%20%3C%2FEM%3E%3C%2FU%3Eappreciated!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1767173%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hi everyone,

 

I have spent an exceptionally long time looking for a way to do this and can't find one (not one that I can use/understand anyway!). What I want to do is;

 

in a SharePoint List I have 3 DateTime columns (because there is no way to have a single column that allows a user to enter date and 'between times' i.e. 7-Oct-20 3:00PM - 4:00PM) and an 'ID' column. 

 

I want to stop users from creating a new item if the DateTime columns in the new item fall within the  period of the existing DateTime columns, if the 'overlapping' DateTime columns have the same item ID.

 

EXAMPLE:

 

ID Column

Date 1

Date & Time 2

Date & Time 3

5555

5-Oct-20

5-Oct-20 4:00PM

5-Oct-20 8:00PM

7777

5-Oct-20

5-Oct-20 5:00PM

5-Oct-20 6:30PM

5555

5-Oct-20

5-Oct-20 7:00PM

5-Oct-20 8:00PM

 

In the above example, I want to stop the user from creating that item (and have an error message or similar come up to say "time already exists" or something to that effect) 

 

Does anyone know how I could achieve this (either by using a 'Flow' or column validation)??

 

Any assistance would be greatly appreciated!!

0 Replies