Avoiding #Circular Reference with Dates

aschneiderheinze1025

I have a column [PDU (Next Update Date)] that tracks the date a notification should go to a Program Developer with a link to a form to provide an update on the progress of a development.

This notification goes out every two weeks, starting 2 weeks after the project enters Stage 1 (in [Status]. The date it enters Stage is stored in [H-Start Stage 1 Date]

I tried using a helper column [PDU (Next Update Date)-Helper] with the following formula:

=IF(TODAY() = [H-Start (Stage 1)]@row, WORKDAY([H-Start (Stage 1)]@row, 10), IF([PDU (Next Update Date)]@row <> "", IF(AND(TODAY() <= [PDU (Next Update Date)]@row, OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3")), [PDU (Next Update Date)]@row, IF(OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3"), WORKDAY([PDU (Next Update Date)]@row, 10), "")), ""))

The formula first checks to see if TODAY() is the same as the date put in [H-Start (Stage 1)], meaning the development just started Stage 1. If so, return WORKDAY([H-Start (Stage 1)]@row, 10), which would be two weeks from now in business days.

If not, then if [PDU (Next Update Date)] <> "" (which it would be blank if [Status] is anything but Stage 1, 2, or 3 because notifications are only sent when the [Status] is Stage 1, 2, or 3), then

• If TODAY() is before the date of the next update/notification ([PDU (Next Update Date)]), then return that same date in [PDU (Next Update Date)]
• If today is past the [PDU (Next Update Date)], return 10 days from that date or WORKDAY([PDU (Next Update Date)]@row, 10)
• Otherwise, return a blank (no additional notifications will go out via the automated workflow)

To test the formula, I manually entered dates in [PDU (Next Update Date)]. The formula in the helper column then works fine. However, when I change [PDU (Next Update Date)] to equal the value in my helper column (=[PDU (Next Update Date)-Helper]), I understandably get a #CIRCULAR REFERENCE error.

I can't use an automated workflow because you can only record the current date in [PDU (Next Update Date)], not a future date or the value in my helper column. I also do not want to request an update every 2 weeks because I we want to capture historical data of what they entered in the form fields over time.

The date in [PDU (Next Update Date)] should stay the same until TODAY is the day after, then [PDU (Next Update Date)] should be updated to the date in the helper column, so I have a biweekly cadence of sending the notification to the Program Developer every two weeks (on a business day).

CASE | STAGE | TODAY | Helper | Next Update |

| 1 | 1 | 3/10/25 | 3/10/25 | 2/24/25 |

| 2 | 1 | 3/10/25 | 3/12/25 | 2/26/25 |

| 3 | 1 | 3/10/25 | 3/22/25 | 3/8/25 |

Case 1: Last notification sent on 2/24, and Helper says next one should go out on 3/10. Next Update should change to 3/10/25 because Today = Helper, automated workflow will run sending out notification, and Helper will recalculate to 3/24/25

Case 2: No change because Today is before Helper

Case 3: No change because Today is before Helper

Georgie

Hi @aschneiderheinze1025,

I believe that I’ve found a resolution to this issue and have addressed it in my comment on your related thread: Send Bi-Weekly Notification to Complete Form. Let me know if my suggestion works or if it isn’t quite what you’re looking for!

Thanks, 

Georgie

Georgie

Hi @aschneiderheinze1025,

I believe that I’ve found a resolution to this issue and have addressed it in my comment on your related thread: Send Bi-Weekly Notification to Complete Form. Let me know if my suggestion works or if it isn’t quite what you’re looking for!

Thanks, 

Georgie