3 IF Statements

Question

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?

Answer 1

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.