Calling all MS Excel Gurus

[SHOOTNSTR8]

I need a formula to enter a date in a cell when another cell is populated with any data. When I use this formula =IF(B1<>"",TODAY(),""), it populates the date which is good but the date updates to the current date the file is reopened. I want that date to stick and not update when the file is opened later. So to specifically know who (cell B) claimed a part number (cell A) on what date (cell C). BTW, I have already tried replacing TODAY with DATE but it no worky.
Thanks.

[SaltwaterSlick]

Maybe try putting a dollar sign $ in front of "TODAY"... That's what you use for "absolute" to anchor a cell from another location in a regular formula...

[Coacheddy]

I like a good challenge on the day off.

So....what kind of “data” is in the cell? Numbers....text...

[Rich1201]

What date are you wanting based off of the other criteria? Are you wanting today's date when updated but not to update again when the book is reopened?

[SHOOTNSTR8]

We use Excel to claim part numbers for our products. To make sure there are no duplicates, someone will go to the next part number in sequence and put their initials next to it and give it a description. I want the next cell to automatically populate the date when their initials are input. So Coacheddy, the answer is text. Rich1201, yes exactly.

[Rich1201]

We use Excel to claim part numbers for our products. To make sure there are no duplicates, someone will go to the next part number in sequence and put their initials next to it and give it a description. I want the next cell to automatically populate the date when their initials are input. So Coacheddy, the answer is text. Rich1201, yes exactly.

Copy this into a new module. Won't take credit I google'd it but I tested and it works. You will have to save workbook as a macro enabled. Basically anything you type into column A it will give you a timestamp in column B. It won't change after closing the book unless you modify that cell in column A again. If you are unfamiliar with macros and need more detailed steps let me know.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A:A")) Is Nothing Then
For Each C In Intersect(Target, Columns("A:A"))
If Not IsEmpty(C) Then
C.Offset(0, 1).Value = Now
Else
C.Offset(0, 1).ClearContents
End If
Next C
End If
End Sub

[SHOOTNSTR8]

Thank you. I probably got over my head on this one. I will play around with your suggestion and see if I can make it work.

[Rich1201]

Thank you. I probably got over my head on this one. I will play around with your suggestion and see if I can make it work.

If you want to PM me your email I can send the file I created to test that seems to be working. It might at least tell you if this is what you're trying to accomplish.

[SHOOTNSTR8]

PM sent

[tmurray]

Why not have whoever is putting their initials in the box be responsible for putting the date too? It would be simple to drop conditional formatting on the date column with a rule saying that if there is anything in the initial column the date column turns red to help remind the user that they also need to enter today's date.

I agree with the above though, it will essentially be a macro that triggers an action to replace the =Today() formula with a copy/paste special values in cells that have data in them.

[SHOOTNSTR8]

Because I am stubborn and can't let it go when I don't know how to do something.

[tmurray]

Because I am stubborn and can't let it go when I don't know how to do something.

Perfect response, I struggle with the same at work. I will go down a completely unproductive rabbit hole to figure something like this out when the obvious/simple answer would have taken 5 minutes or less to implement.

[Etxbuckman]

Excel makes my head hurt.

[Jason]

Can you manually type the date you want referenced, and then have all the cells reference that day? I'm not sure how else you could formulate that? "today" will always pull in the current date. You could do a today +/- but that will change daily as well. I think you'll have to hard-code at least one day.