fixed date

Brass Contributor

Hello,

 

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
Ahmad,
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.

Rich

thanks! 

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

 

thanks!

 

Ahmad

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

 

best,

Ahmad