Skip to main content

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

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. 

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

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

Reply


Terms & Conditions