History of Dates to be traced in Excel (2010) in DD/MM/YYYY format

[Gajendran Yadhav]

Hi all,

I'm working on a file where in I am tracking a series of incidents that occur on certain dates.

For this I have written a macro. This works. but only issue I'm facing is, this automatically stores the data in MM/DD/YYYY format that too only for dates [1 - 12] / MM / YYYY.
for dates [13 - 31] / MM / YYYY, it takes the actual input as it is.

This happens only at the first entry. On subsequent entries, this issue is not arising. It stores the date in correct format. (refer pic below)

I have data validation in the input cell (column M) for the users to enter the date in DD/MM/YYYY format only.

My code is here..

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    Dim dateList As String
    Dim existingList As String
    Dim lastRow As Long
    Dim btn As Button

    ' Find the last row in column M with data
    lastRow = Me.Cells(Me.Rows.Count, "M").End(xlUp).Row
  
    ' Check if the changed cell is within column M and from M5 onwards
    If Not Intersect(Target, Me.Range("M5:M" & lastRow)) Is Nothing Then
        ' Iterate through each changed cell in column M
        For Each cell In Intersect(Target, Me.Range("M5:M" & lastRow))
            ' Check if the cell in column M has a date value
            If IsDate(cell.Value) Then
                ' Retrieve existing date list from corresponding row in column G
                existingList = Me.Cells(cell.Row, "G").Value
              
                ' If the existing list is not empty, append a comma and space
                If existingList <> "" Then
                    existingList = existingList & ", "
                End If
              
                ' Append the new date to the existing date list
                existingList = existingList & Format(cell.Value, "dd/mm/yyyy")
              
                ' Write the updated date list back to the corresponding row in column G
                Me.Cells(cell.Row, "G").Value = existingList
            End If
        Next cell
    End If

Please help to resolve this...

Pictorial representation:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query 2:
I have the Command Button in column U (ref pic).
is it possible to protect that particular row (A:T & V, W) alone on clicking the button.

Note that column X1 : last is already protected and hidden. worksheet protected.
Column G is also protected as it obtains the date from column M.
User will be able to input data only in cells B to T. (Column A is protected - value is default population of S.No.s based on entry in subsequent column B)
Column U is button.
V & W - asks question on clicking the button and stores the answer in to V & W.


Any help is much appreciated.
Thanks in advance.

 

[Gajendran Yadhav]

The way it looks to me your code works up to the last statement.
Me.Cells(cell.Row, "G").Value = existingList
At this point when you only have a single date value (First Entry) and it writes it out to the spreadsheet it takes your dd/mm/yyyy string value, recognises as a date and writes it out to the spreadsheet as a date creating the US format issue in the process. On second and subsequent entries the write out value is clearly a string and no conversion takes place.

I have tried Mark's code on multiple cells and it works.

Other options are rely on the fact that you are concatenating dates which indicates that you are happy for this column G to be Text.
(the below does mean that it will no longer recognise single dates as dates in terms of doing any date calculations with it, so Mark's option is more flexible)

Options:
1) Format Column G as TEXT OR
2) Before that write line format the cell as TEXT
eg add the line in Blue

                ' Write the updated date list back to the corresponding row in column G
                Me.Cells(cell.Row, "G").NumberFormat = "@"
                Me.Cells(cell.Row, "G").Value = existingList

PS: Your code is looping (calling itself), suggest you Turn Off/On EnableEvents
Application.EnableEvent =FALSE at the start and Application.EnableEvent =TRUE at the end

Hi @Alex Blakenburg & @MARK858

It's great that your code worked. Thanks a lot.

PS: Your code is looping (calling itself), suggest you Turn Off/On EnableEvents
Application.EnableEvent =FALSE at the start and Application.EnableEvent =TRUE at the end

And btw, I have incorporated the snippet as well. Thanks for the suggestion as well.

 

[MARK858]

You're welcome, happy we could help