Circular Reference Issue

Shawn R

So I have two fields I am trying to create a formula. The first field is Today's Date, in which it is autopopulated with the current date. I have a second field name Calendar End Date, I am trying to create the formula in this field.

 

What I am trying to do is create a check that if Today's Date is greater than the Calendar End Date then delete the contents of Calendar End Date. The Calendar End Date is already populated by another forumal that is drawing information from other fields, but regardless if I create a new sheet with just these two fields I cannot seem to get the formula to work without getting a circular reference error.

 

So in the Calendar End Date I have this formula.

 

=IF([Today's Date]2 > [Calendar End Date]2, "")

 

I know that the circular reference error comes from the fact that the formula references the field in which it is written. But I need to reference that field in order for the forumla to work as intended. I also cannot create another field to accomplish this due to the purposes of the field and the current formula in it.

 

Any possible way to accomplish this?

Shawn R

Scratch this question, I was able to solve it.

QueenD

@Shawn R How were you able to solve your problem? Please share your final formula with us. Thanks!

Shawn R

@QueenD This is my Final Formula.

 

=IF([Return Date]2 < [Today's Date]2, Delete2, IF([Approved?]2 = 1, IF([First Date Absent]2 < [Return Date]2, [Return Date]2 - 1, IF([First Date Absent]2 = [Return Date]2, [Return Date]2, ""))))

I created the delete field as a permanent empty field and referenced it to populate the field the formula is written in. This allows me to delete the contents of the Calendar End Date column without the circular reference issue.

QueenD

Thank you Shawn!