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

 

[MARK858]

1 = date returns 8/7/2018 as opposed to 7/8/2018

If you want the format "dd/mm/yyyy" then 08/07/2018 is correct as today is the eighth day of the seventh month

 

[Peter_SSs]

1 = date returns 8/7/2018 as opposed to 7/8/2018

I agree with Mark - you said you wanted dd/mm/yyyy so that's what I did.

.. but if you remove the y , the date remains.

OK, (still with dd/mm/yyyy) ...

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")
      Else
        Range("B" & c.Row).ClearContents
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[Trevor3007]

Hi mark858,

many thanks for getting back to me. I checked the format prior to send my last and although set as dd/mm/yyyy it returns the US date (month/day/yyyy)8/7/18 as opposed to UK 7/8/18

KR
Trevor3007

 

[MARK858]

UK date for today is 08/07/2018 (dd/mm/yyyy) not 07/08/2018 which is US format.

We are in July, the seventh month.

If you actually mean it is returning 07/08/2018 then that makes sense as it is what it returns for me.

 

[MARK858]

If you actually mean it is returning 07/08/2018 then that makes sense as it is what it returns for me.

And if that is the case try...

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
                With Range("B" & c.Row)
                    .Value = CDate(Date)
                    .NumberFormat = "dd/mm/yyyy"
                End With
            Else
                Range("B" & c.Row).ClearContents
            End If
        Next c
        Application.EnableEvents = True
    End If
End Sub

 

[Trevor3007]

hi,

this is what it returns....

Date Comp

07/08/2018 as you can see it is displaying USA date format (month/day/year) I have checked the formatting and its set to dd/mm/yyyy - UK format

<colgroup><col></colgroup><tbody>
</tbody>

 

[MARK858]

Have you tried the code that I posted?

 

[Trevor3007]

Hi mark,

thanks ..I must of still be using Peter's . date now UK. But need to protect certain columns & when I put protect sheet, put Y in col g , I get an error & your code does not work. Also if I remove a Y after I put the protection on & get the error, the date remains . Where as when I don't have any protection on, your code works FAB

mmmm...
but thank you.

 

[MARK858]

Unprotect the sheet at the start of the code and then set the protection at the end of the code.

 

[Trevor3007]

FANTASTIC,,,,,,

all working & no further issues.

thank you very, very much....