retain date

[Trevor3007]

Hi,

I use the the code below:-
=IF(G2="y",TEXT(TODAY(),"dd/mm/yyyy"),"")

which work great until the next day, when yesterdays day is now the present day. Is there a way to retain the original date ?

Many thanks in advance.

KR
Trevor3007

 

[arthurbr]

Various solutions at https://trumpexcel.com/date-timestamp-excel/ (see paragraph "Circular References Trick to Automatically Insert Date and Timestamp in Excel")

 

[Trevor3007]

thanks for your swift reply & assistance thereof...

I had a look and yes there is plenty but I am unsure which to choose & politely request if you could choose the best & supply the applicable code?

Many thanks
Trevor3007

 

[Peter_SSs]

This is normally done with vba code. Would that be acceptable?
If so, does G2 have a formula that might return "y" or is G2 entered manually?
If a formula, what is the formula in G2?
What column do you want the date in?

 

[Trevor3007]

Hi Perter_SSs,

=IF(G2="y",TEXT(TODAY(),"dd/mm/yyyy"),"") is in B2 . The =IF(G2="y",TEXT(TODAY(),"dd/mm/yyyy"),"") in the 2 worksheet. This particular worksheet tab changes its name weekly so I hope this wont by an issue?

thanks again

thank you for your time

 

[Peter_SSs]

OK, so it seems that you want the date in column B.
What about my other questions?

This is normally done with vba code. Would that be acceptable?
If so, does G2 have a formula that might return "y" or is G2 entered manually?
If a formula, what is the formula in G2?

 

[Trevor3007]

DOH...sorry I had doen so.

when Y is manually placed into G2, B2 then puts in the date....

Hope this has now given you sufficient info :}

 

[Trevor3007]

ooops...spelling error in previous detected!!

OH...sorry thought I had already explained .....

​

 

[Peter_SSs]

Try this Worksheet_Change code in a copy of your workbook. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("G"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If LCase(c.Value) = "y" Then Range("B" & c.Row).Value = Format(Date, "dd/mm/yyyy")
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[Trevor3007]

many thanks...

2 isues
1 = date returns 8/7/2018 as opposed to 7/8/2018
2 = when cell b2 has y in shows date, but if you remove the y , the date remains

sorry for the bother.