0

So, I would like to Compare if any of my Dates from List "A" is between my StartDate and EndDate from List "B". I am new to sharepoint and any Article can show step by step would be greatly appreciated.

LIST "A"

Holiday Dates
1 05/12/2021
2 07/04/2021

LIST "B"

STARTDATE Dates YES/NO (CALCULATED FIELD)
05/01/2021 05/18/2021 YES
06/01/2021 07/03/2021 NO

need a calculated field like below:

YES/NO (CALCULATED FIELD)=  IF LISTA.DATES BETWEEN LISTB.DATE THEN YES ELSE NO

OR

Hardcoding those days?

1 Answer 1

0

Lookup column (and its additional fields) cannot be referred in the Calculated column formula, if that is what you plan to do.

There are a few workarounds to achieve this requirement, for example:

  1. Build a SharePoint 2013 workflow / Microsoft Flow to retrieve the corresponding value from List A and then fill it in another column in List B, so that it can be used in the calculated column formula.
  2. Use workflow / flow to do the comparison and update the YES/NO field directly (without using calculated column and formula). It should be a Text or Yes/No column in this scenario.

You can see the reference here for a tutorial on using SharePoint 2013 workflow: Using Lookup Field in Calculated Column SharePoint.

And the calculated column formula should be:

IF(AND([<Ref.>]<=[ENDDATE],[<Ref.>]>=[STARTDATE]),"YES","NO")

Reference: IF function.

1
  • I used your suggestion and hardcoded my calculated column. Thanks!
    – Nil R
    Feb 10, 2021 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.