Date() function creating circular reference

Question

Date() function creating circular reference

Jim Ballas 0

Recently I have been getting the message that I have circular references in my Excel worksheets using version Microsoft® Excel® 2021 MSO (Version 2607 Build 16.0.20228.20124) 64-bit. I have been trying to track them down and its been driving me nuts. So I think I have tracked it down to the Date() Function. Every cell that uses the date() function is indicating a circular reference. As a test I created a new blank worksheet and added only +Date() in a single cell and I get a circular reference. It seems like there is some sort of bug maybe in the latest update or something. Unless I am missing something totally but a blank worksheet with only a single formula should not be indicating a circular reference. If I am doing something wacky any help would be appreciated. Thanks

Jim Ballas 0 Reputation points

2026-08-11T01:26:42.32+00:00

My mistake in this post it is the today() function not the date() function that I am having trouble with
Jim Ballas 0 Reputation points

2026-08-11T01:37:23.4+00:00

It seems that the now() function is also creating the same behavior
Jim Ballas 0 Reputation points

2026-08-11T03:15:13.9666667+00:00

Ok so after doing some digging I did find a circular reference and fixed it. But here is the deal, that one circular reference in one sheet while that sheet was open made ANY cell in any open worksheet with either the today() or the now() function show as a circular reference. It was impossible to go one by one and delete each today() function as there were hundreds maybe thousands. So through process of elimination I found the one workbook that would make the circular errors appear when open. Made a backup of that workbook. Then deleted sheet by sheet in that workbook until the errors disappeared, then copied from backup and went through that sheet to find the circular reference. I don't know why Excel would show every cell with either today() or Now() as circular when there is a circular reference but to me that sounds like a bug. Anyway I am all set now. Hope this helps someone else.

1 answer

Your answer

Jim Ballas 0 Reputation points

2026-08-11T01:26:42.32+00:00

My mistake in this post it is the today() function not the date() function that I am having trouble with
Jim Ballas 0 Reputation points

2026-08-11T01:37:23.4+00:00

It seems that the now() function is also creating the same behavior
Jim Ballas 0 Reputation points

2026-08-11T03:15:13.9666667+00:00

Ok so after doing some digging I did find a circular reference and fixed it. But here is the deal, that one circular reference in one sheet while that sheet was open made ANY cell in any open worksheet with either the today() or the now() function show as a circular reference. It was impossible to go one by one and delete each today() function as there were hundreds maybe thousands. So through process of elimination I found the one workbook that would make the circular errors appear when open. Made a backup of that workbook. Then deleted sheet by sheet in that workbook until the errors disappeared, then copied from backup and went through that sheet to find the circular reference. I don't know why Excel would show every cell with either today() or Now() as circular when there is a circular reference but to me that sounds like a bug. Anyway I am all set now. Hope this helps someone else.

Answer 1

DATE() by itself should not create a circular reference. A circular reference happens when a formula refers to its own cell, either directly or indirectly through other formulas.

For this issue, use these checks:

In Excel, go to Formulas > Error Checking > Circular References.
- Select the first cell listed there to trace the dependency chain.
- If the list points to cells using DATE(), the problem is likely an indirect reference elsewhere in the workbook, not the DATE function itself.
Check whether the formula is actually DATE(...) or a different date-related formula.
- DATE requires arguments.
- If the formula uses NOW() or another formula combined with cells that eventually point back to the same cell, Excel can flag that as circular.
If the formula was entered in a cell that is also included in a range used by that formula or by a dependent formula, move the formula to another cell and test again.
- Example from Microsoft guidance: a formula like =SUM(A3:F3) entered in F3 creates a circular reference because it includes itself.
Check calculation mode.
- Go to File > Options > Formulas.
- Under Workbook Calculation, make sure Automatic is selected.
- This does not cause circular references by itself, but it can help with formula behavior if calculations seem inconsistent.
If the circular reference is intentional, enable iteration:
- File > Options > Formulas
- Turn on Enable iterative calculation
- By default, Excel stops after 100 iterations or when values change by less than 0.001.
- Only use this if the circular reference is expected.

If a brand-new blank worksheet with only one formula still shows a circular reference, the practical next step is still to use Formulas > Error Checking > Circular References and see which cell Excel reports. That identifies whether the reference is direct or indirect.

References: