retain date

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
675
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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) ...
Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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...

Code:
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
 
Upvote 0
hi,

this is what it returns.... [TABLE="width: 121"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Date Comp[/TD]
[/TR]
[TR]
[TD]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
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
Unprotect the sheet at the start of the code and then set the protection at the end of the code.
 
Upvote 0
FANTASTIC,,,,,,

all working & no further issues.

thank you very, very much....
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,331
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top