Complex Nested IF AND statement (dates and regions)

[hannahRFP]

I have a complex formula I am trying to write, and unfortunately it says there is an error in my code, but I can't seem to work out what! 

I am trying to write a custom field that says LIVE, FUTURE or COMPLETED depending on the number of days away an event is, and in what region. For example, I want a project to be live if its 180 days away or closer for AUS/NZ, but only want a project to be live at 120 days for EMEA. 

Here is the code I have written so far, which doesn't work (for now I have simplified to just be LIVE or COMPLETED, as once I have worked this out I'm sure I'll be able to add future!): 

IF(
AND(
(OR(Territory = "SEA",Territory = "AUS/NZ")),
AND( DATETIME_DIFF(RHO, TODAY(), 'days') < 180, DATETIME_DIFF(OPEN, TODAY(), 'days') >= 0),
),
"LIVE",

IF(
AND(
(OR(Territory = "CAN",Territory = "NAM",Territory = "EMEA",Territory = "MENA")),
AND( DATETIME_DIFF(RHO, TODAY(), 'days') < 120, DATETIME_DIFF(OPEN, TODAY(), 'days') >= 0),
),
"LIVE",

IF(DATETIME_DIFF(OPEN, TODAY(), 'days') < 0, "COMPLETED")
)
)

 I have tried to combine two pieces of working code to make this. 

Code 1 - Showing live or completed just for 120 days or less 

IF(
AND( DATETIME_DIFF(RHO, TODAY(), 'days') < 120, DATETIME_DIFF(OPEN, TODAY(), 'days') >= 0), "LIVE",
IF(DATETIME_DIFF(OPEN, TODAY(), 'days') < 0, "COMPLETED")
)

 Code 2 - Showing numbers based on concept and region 

IF(
AND(
Territory = "EMEA",
(OR(Concept = "A",Concept = "B",Concept = "C"))
),
1900,


IF(
AND(
(OR(Territory = "CAN",Territory = "NAM",Territory = "SEA",Territory = "AUS/NZ")),
(OR(Concept = "A",Concept = "B",Concept = "C"))
),
1650,


IF(
(OR(Concept = "D",Concept = "E")),
15000
)
)
)

 Any help or tips would be greatly appreciated! It seems the issue is with nesting an IF statement with both an OR and an AND. 

Thanks

[Databaser · Accepted solution]

Yeah, some troubles in your formula indeed.

Try:

IF(AND(OR(Territory="SEA",Territory="AUS/NZ"), DATETIME_DIFF(RHO, TODAY(), 'days')< 180, DATETIME_DIFF(OPEN, TODAY(), 'days')>= 0), "LIVE", IF(AND(OR(Territory="CAN", Territory="NAM", Territory="EMEA", Territory="MENA"), DATETIME_DIFF(RHO, TODAY(), 'days')< 120, DATETIME_DIFF(OPEN, TODAY(), 'days')>= 0), "LIVE", IF(DATETIME_DIFF(OPEN, TODAY(), 'days')< 0, "COMPLETED")))

Haven't double checked it myself. 

[hannahRFP]

Hi @Databaser, thanks for the reply! 

Your solution worked, thank you! I then added in a few additional formula to show 'FUTURE' and also to add in projects that only have 'Refit start' dates and no RHO and OPEN dates. 

I made it simpler by adding a 'earliest dates' field and used this for some of the formula. I had to write its own lines for Refits because they don't have an open date, so the earlier ones don't work.

IF(
AND(
OR(Territory="SEA",Territory="AUS/NZ"), 
DATETIME_DIFF({Earliest date}, TODAY(), 'days')< 180, 
DATETIME_DIFF(OPEN, TODAY(), 'days')>= 0), 
"LIVE", 


IF(
AND(
OR(Territory="CAN", Territory="NAM", Territory="EMEA", Territory="MENA"), 
DATETIME_DIFF({Earliest date}, TODAY(), 'days')< 120, 
DATETIME_DIFF(OPEN, TODAY(), 'days')>= 0), 
"LIVE", 


IF(
AND(
OR(Territory="SEA",Territory="AUS/NZ"), 
DATETIME_DIFF({Earliest date}, TODAY(), 'days')> 180), 
"FUTURE", 


IF(
AND(
OR(Territory="CAN", Territory="NAM", Territory="EMEA", Territory="MENA"), 
DATETIME_DIFF({Earliest date}, TODAY(), 'days')> 120),
"FUTURE",


IF(
DATETIME_DIFF(OPEN, TODAY(), 'days')< 0, "COMPLETED",


IF(
AND(
DATETIME_DIFF({Refit start}, TODAY(), 'days')< 120, 
DATETIME_DIFF({Refit start}, TODAY(), 'days')>= 0), 
"LIVE", 


IF(
DATETIME_DIFF({Refit start}, TODAY(), 'days')< -1, "COMPLETED")
)
)
)
)
)
)

For anyone who is interested, the 'earliest date' formula is below:

IF(
  OR(
    SFC,
    RHO,
    OPEN,
    {Refit start}
  ),
  DATETIME_PARSE(
    MIN(
      IF({SFC}, VALUE(DATETIME_FORMAT({SFC},'x')), 999999999999999),
      IF({RHO}, VALUE(DATETIME_FORMAT({RHO},'x')), 999999999999999),
      IF({OPEN}, VALUE(DATETIME_FORMAT({OPEN},'x')), 999999999999999),
      IF({Refit start}, VALUE(DATETIME_FORMAT({Refit start},'x')), 999999999999999)
    ) ,
    'x'
  )
)

If there's anything I can do better in this code please let me know! 

Thanks again for your help

[Databaser]

Glad I could help. Would you mark an answer as "solved" so others can find it to?