fixed date

ahmad ali



I have this problem: I need when I write something in cell B1 then G1 gives me the date(date of writing).

I've tried this formula  in G1:        =IF(B1<>"";NOW();"")  but it doesn't work because whenever I open the file next day it gives me the date for that next day, not the date when I wrote something in cell B1


I hope it's clear


thank you in advance


4 Replies
The NOW() function always returns the current date and is updated when you open the workbook, as you have found out. The only way to do as you wish would be to either manually type the date into the cell or use COPY and PASTE SPECIAL in the cell to overwrite the NOW() function. Finally you could write a macro to add the date.



I thought there could be a way to stop the "refresh" or other function  to do the job , some functions related to " DATE "






Hi Ahmad,


Formulas are not capable to do that!

You have to use a VBA code instead.


I would suggest this code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("G1") = Now
End If

On Error GoTo 0
End Sub


Please check out my replies to this post which is similar to what you asking for.


Hope that helps

@Haytham Amairah Thank you so much .... and I'm very sorry for the delayed response 


thank you again





Related Conversations
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies