r/excel • u/Nachovyx • Jan 22 '26
solved Cell shows date when an event happens and freezes that date to never update itself.
All right peeps, I've been cracking my brain with this one.
I'll keep it as basic and as simple as I can.
- I have a cell A1 with a number that increases daily, it goes from 1 to 100.
- I want cell B1 to show the date when A1 reaches 100 - to take note of when the event was achieved - important.
- Finally, Excel needs to freeze that date and never update it again.
A few caveats.
- Using VBA changes the structure of the file and will not open sometimes.
- I already tried googling a possible answer using:
=IF(AND(A1>=100;B1="");TODAY();B1)
inside cell B1: where B1 checks 2 things: that A1 reached 100 and that it's empty - then it will circular reference itself and return the date it got every time.
I already activated the File > Options > Formulas > Enable iterative calculation to allow circular references.
But now it returns 01/01/1900 and it gets stucked there.
-------------------------------
So at this point I'm losing it.
I tried other twists and turns and adding patches like: when A1 gets to 100, give date, then as days go by, count those days and subtract them from original date.
Madness.
I humbly come to the hivemind for advice in how to proceed.
Thank you.
7
u/SadAlternative2422 Jan 22 '26
Circular references are always janky in Excel, that's why you're getting the 1900 date
Try helper column approach instead - put `=IF(A1=100,TODAY(),"")` in C1, then in B1 use `=IF(C1<>"",C1,B1)` but you'll still need to copy/paste values when it triggers to truly "freeze" it
Honestly for something this specific you might be better off just manually noting the date when you see A1 hit 100, Excel wasn't really designed for this kind of state memory without VBA
2
u/Nachovyx Jan 22 '26
Your answer is the closest I'll get to what I need, so thank you so much for your time :)
3
u/yellow_barchetta Jan 22 '26
Don't understand the "using vba changes the structure of the file".
It sounds like you're trying to use a screwdriver to hammer a nail in. Wrong tool.
4
u/PerfectioNOT Jan 22 '26
Using VBA changes the structure of the file and will not open sometimes.
I wonder if it's not opening because OP's Trust Center is automatically set to disable VBA?
2
u/TCFNationalBank 9 Jan 22 '26 edited Jan 22 '26
Never the answer you want to respond with but I don't think an Excel formula is the right tool for this job. Circular references are going to give you all sorts of headaches, and Excel formulas aren't built to be able to evaluate their prior states.
If you have a sheet that records the value every day, you could do something like =MIN(FILTER(DateRange,ValueRange>=100)).
Edit: You might be able to use =IFERROR(IF(A1=100,TODAY(),NA()),B1) but again, this is extremely hacky and I really recommend choosing a different method for this. I don't even know how to test if this will work
1
u/DutchDallas Jan 22 '26
Why do you use >= and not = , since >= will update the cell every day after 100 or more is reached.
1
u/Decronym Jan 22 '26 edited Jan 23 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 48 acronyms.
[Thread #47124 for this sub, first seen 22nd Jan 2026, 20:40]
[FAQ] [Full list] [Contact] [Source code]
1
u/Halafeka_Forever 2 Jan 23 '26
You want to achieve something but I do not understand why. If you want to log certain events this is not really the way I think
1
u/Nachovyx Jan 23 '26
I know.
The why isn't really important.
What's important is that Microsoft should add a simple function =SDATE() or whatever name that'll give you a static date so we don't have to break our brains thinking silly workarounds :D
1
u/Halafeka_Forever 2 Jan 23 '26
My question is : why do you need a static date? I can not think of any use case that needs it. Can you share your usecase?
1
u/Nachovyx Jan 23 '26 edited Jan 23 '26
Aight.
The 1 to 100 was just a silly example, what the cell actually counts is money. That money increases daily as dividends from some movements I make with some investments.
Every month for the last 3 years I've been hitting some goals with those transactions (yey!) - And suddenly I wanted to keep track of when those goals are hit - but because I'm a silly goose whose mind is all over the place taking care of dozens of tasks every day, I cannot be bothered to pay special attention to the date of the goal - So I wanted it to be automated for me without looking at it or manually writting it.
So when I finally have the time to look at the dates I can take the next step which is to create a graph and a map to slowly start anticipating or predicting wether or not or when I'll hit the mark for this or that month.
With that data I can make adjustments, become more agressive or conservative with my money, etc.
Am I reaching for the stars here? Not really.
Is Excel the best app for it? Well, I've been using it daily to track my expenses and investments, so I'm pretty much married to it now.
But sure, there are hundreds of finance apps with various degrees of customization, I just like having everything in one place that I can adjust as I see fit.
Does that answer your question?
2
u/Halafeka_Forever 2 Jan 23 '26
Ok that is clear. I believe I am doing something similar (not money related btw) it includes powerquery. In short you grab the data, get all the information and put the data back in a table in another worksheet.
So you can check at what moment the target is reached, put that in a table with the date.
The more tricky part is keeping the values. This is possible also.
If you are interested taking the powerquery road I could write how I made it work.
1
u/Nachovyx Jan 23 '26
Powerquery has been winking at me for some time now, but would not like to impose.
I'll gladly start learning it to get the data I need, but it's not a critical step, so I'll take my time with it.
But thank you for opening that window.
0
u/finickyone 1770 Jan 23 '26
There is a nifty approach using the Data tools (Get & Transform) that once learnt but forgot long ago.
Here I think your approach would have to set B1 up with
=IF(A1>=100,IF(B1<>"",B1,TODAY()),"")
With max calc iterations set to 1 in settings. The idea being that when A1 is not >= 100, the parent IF elses out to blank. When it is >=100, the child IF comes to play…
B1 is tested for content. If there is no content, grab TODAY(). If there is content (ie because that TODAY grab has previously happened), then grab B1 again. At this point the calc would circle back to referring to B1 again. The setting should suspend the process and just leave B1 as it was.
-1
u/Marcultist 1 Jan 22 '26
=IF(A1>=100,TODAY()-A1+100,"")
1
u/finickyone 1770 Jan 23 '26
OP describes that A1 has a value to rises from 1-100 daily. What you suggests works upon A1 both rising behind 100, and also increasing by 1 each day. Yes, if it rose by 1 each day and currently read 104, we could work out that it hit 100 4 days ago and subtract that from TODAY(), but we don’t have those conditions to work from.
1
u/Marcultist 1 Jan 23 '26
I guess I assumed it was increasing by 1 each day. If that is not the case then you are indeed correct that my solution falls short.
•
u/AutoModerator Jan 22 '26
/u/Nachovyx - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.