0

I have a calculated column which should return DepA, DepB or DepC according to the multiple IF statement which I cannot work out correctly!!!

I have a DateRequest which is entered by the user. I then have a ExpireDate which is a calculated column with the following formula

=IF(DAY(Created)<11,DATE(YEAR(Created),MONTH(Created)+1,0),DATE(YEAR(Created),MONTH(Created)+2,0))

What I need is: DateRequest is within the next 7 days should return DepA in the Department column (DateRequest = 08/02/21 today is 06/02/21 Department = A)

If Date request is over the next 7 days but within ExpireDate should return DepB in the Department column (DateRequest 17/02/21 today is 06/02/21 ExpireDate 28/02/21 Department = B)

If DateRequest is after ExpireDate should return DepC (DateRequest 05/03/21 today is 06/02/21 ExpireDate 28/02/21 Department = C)

I have tried the below two formulas unsuccessfully:

=IF((TODAY()+7>DateRequest, “DepA”), (IF(DateRequest)=<Expire),"DepB", “DepC”)

=IF(TODAY()+7>DateRequest, “DepA”), (IF(DateRequest)=<Expire),"DepB"), (IF(DateRequest)>Expire),"DepC")

What formula should I use, or is it even possible to incorporate ExpireDate formula in a single statement?

0

You can try this:

Calculated Column DEPT:

=IF((DateRequest<=TODAY()+7),"DepA",IF(AND(DateRequest>(TODAY()+7),DateRequest<=Expire),"DepB","DepC"))

Note: Since [Expire] is based on [Created] date, I would replace TODAY() with [Created] for this formula as well, otherwise DEPT value will change if any column value is changed.

enter image description here

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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