Sharepoint list calculation using IF and AND statement

Question

Hi,

I am trying to fix a calculation written into a column in a SharePoint list;

this is the calculation, it isn't returning a syntax error, but it isn't working:

=IF(RevisedResponseTargetDate="","",

IF(AND(ResponseSentDate="",RevisedResponseTargetDate<[Todays Date]),"No",

IF(ResponseSentDate>RevisedResponseTargetDate,"No","Yes"))))

what I want it to do is

'if column A is blank, return blank,

else if column B is blank and column C is earlier than column D, return "no"

else if column B is later than column C, return "no",

else return "yes"

but currently it is returning "Yes" even when column b is blank. Have I got the syntax wrong, or are there too many arguments? Is it the IF(AND that I haven't got right?

Thanks

Answer 1

Hi Carmel,

We tested at my end by using the formula you provided above, =IF([RevisedResponseTargetDate A]="","",IF(AND([ResponseSentDate B]="",[Column C]<[Todays Date]), "No",IF([ResponseSentDate B]>[Column C],"No","Yes")))). We got syntax error if we set ResponseSentDate, RevisedResponseTargetDate and Todays Date as Date and Time type. Moreover, there are only 3 items in this formula so I think column C is missing in it.

Moreover, we created another 4 single line text columns named A,B,C and D with a Calculated column named RESULT. Here is the formula and the result we got:

I would like to know the column type you are using cause there is no related calculations for Date and Time column. For your reference: https://support.microsoft.com/en-us/office/examples-of-common-formulas-in-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3 It may need some complicated to meet your requirement.

Best Regards,

Ivy

Answer 2

Hi Ivy,

I'm not sure I understand why it matters if the columns are date/time, are you saying the calculation won't work if they are all set as date/time?

Answer 3

Hi Carmel,

I assume that there is no relevant formula inthis article. It may be because time and date cannot be simply compared. Besides， I got syntax error when I try to test at my end.

In the examples, we can use Date and Time to calculate. Your request may not be determined by comparing the time using the IF function, the formula could be quite complicated. As our scope is the OOB issue of Office and SharePoint, we kindly suggest you post the related query in Microsoft Q&A, the expert will provide further suggestions for you.

Appreciate your understanding. Have a nice day!

Best Regards,

Ivy