Add a date from another field into Calculated Field

[Donna_Boj]

Hello All,

Hoping someone can help.

I need to add a date from another field into a calculated field if a certain field contains a specific team.

I have an existing field with some complicated calculations to determine a 'Latest Briefing Date' based off a requested date field but one team wants this calculation to be ignored just for them.
When their team name is selected in one field they just want the 'Requested Broadcast Date' to auto populate into the 'Latest Briefing Date' Field.

Below is the current calculation we have in the 'Latest Briefing Date' Field.

IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=1,{DE:oracleBriefingWeeksPrior}="REQUESTED BROADCAST DATE CANNOT BE A SATURDAY OR SUNDAY", IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=7,{DE:oracleBriefingWeeksPrior}="REQUESTED BROADCAST DATE CANNOT BE A SATURDAY OR SUNDAY", IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})<=4,ADDDAYS({DE:oracleRequestedBroadcastDate},-7*{DE:oracleBriefingWeeksPrior}),IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=5,ADDDAYS({DE:oracleRequestedBroadcastDate},(-7*({DE:oracleBriefingWeeksPrior}-1))-3),IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=6,ADDDAYS({DE:oracleRequestedBroadcastDate},(-7*({DE:oracleBriefingWeeksPrior}-1))-4),{DE:oracleRequestedBroadcastDate})))))

I need to ensure this code is ignored if their team ID 'OpsPaperless' shows in the Team ID field and the 'Requested Broadcast Date' auto populates into this field 'Latest Briefing Date'.

I need something like this adding:

Ignore above code IF{DE:oracleTeamID}=OpsPaperless, then add {DE:oracleRequestedBroadcastDate}

Thanks in advance!!

[lgaertner · Accepted solution]

Hello Donna,

without having the possibility to try it in detail, I think using your last formula as a kind of wrapper around your current calculation could be the solution:

As the IF formula works like this:

IF(condition, trueExpression, falseExpression)

You could replace condition by the check for the teamID, trueExpression by the Requested Broadcast Date and falseExpression by your calculation.

IF({DE:oracleTeamID}=<teamID>,{DE:oracleRequestedBroadcastDate},IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=1,{DE:oracleBriefingWeeksPrior}="REQUESTED BROADCAST DATE CANNOT BE A SATURDAY OR SUNDAY", IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=7,{DE:oracleBriefingWeeksPrior}="REQUESTED BROADCAST DATE CANNOT BE A SATURDAY OR SUNDAY", IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})<=4,ADDDAYS({DE:oracleRequestedBroadcastDate},-7*{DE:oracleBriefingWeeksPrior}),IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=5,ADDDAYS({DE:oracleRequestedBroadcastDate},(-7*({DE:oracleBriefingWeeksPrior}-1))-3),IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=6,ADDDAYS({DE:oracleRequestedBroadcastDate},(-7*({DE:oracleBriefingWeeksPrior}-1))-4),{DE:oracleRequestedBroadcastDate}))))))

Regards

Lars

[Donna_Boj]

Thank you Lars,

This helped us figure out how to fix the formula!
We went with this in the end, which works and is pretty much what you suggested.

IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=1,{DE:oracleBriefingWeeksPrior}="REQUESTED BROADCAST DATE CANNOT BE A SATURDAY OR SUNDAY",

IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=7,{DE:oracleBriefingWeeksPrior}="REQUESTED BROADCAST DATE CANNOT BE A SATURDAY OR SUNDAY",

IF({DE:oracleTeam}="OpsPaperless",{DE:oracleRequestedBroadcastDate},

IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})<=4,ADDDAYS({DE:oracleRequestedBroadcastDate},-7*{DE:oracleBriefingWeeksPrior}),

IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=5,ADDDAYS({DE:oracleRequestedBroadcastDate},(-7*({DE:oracleBriefingWeeksPrior}-1))-3),

IF(DAYOFWEEK({DE:oracleRequestedBroadcastDate})=6,ADDDAYS({DE:oracleRequestedBroadcastDate},(-7*({DE:oracleBriefingWeeksPrior}-1))-4),{DE:oracleRequestedBroadcastDate}))))))