Add a date from another field into Calculated Field | Community
Skip to main content
Donna_Boj
Donna_Boj
Level 3
July 5, 2024
Solved

Add a date from another field into Calculated Field

  • July 5, 2024
  • 1 reply
  • 640 views

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!!
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by lgaertner

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

1 reply

lgaertner
lgaertnerAccepted solution
Level 9
July 8, 2024

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
    Donna_BojAuthor
    Level 3
    July 9, 2024

    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}))))))
    Terms & ConditionsAccessibility statement

    Loading footer...