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.

 

[Akuini]

Try formatting the cells in col M as "dd/mm/yyyy".

 

[Gajendran Yadhav]

Try formatting the cells in col M as "dd/mm/yyyy".

Dear,

Column M5:M LastRow already has Data Validation. It allows only DD/MM/YYYY format.
yet I'm facing this issue...

 

[bobaol]

can you break up your data into more granular items, like put your data in cell AG, then use the empty columns to work with it? also, it looks like your original date is Text. I'm assuming the original date is text.

'	'	'	'	'	'	'	'
'assume your data is in cell AG17 '06-12-2024	'	'	'	'	concatenate as text	format	format
06-12-2024	06	06-12	12	2024	12/06/2024	12/6/2024​	6-Dec-24​
	=LEFT(AG17,2)	=LEFT(AG17,5)	=RIGHT(AI17,2)	=RIGHT(AG17,4)	=AJ17&"/"&AH17&"/"&AK17	=DATEVALUE(AL17)	=AM17

 

[Gajendran Yadhav]

can you break up your data into more granular items, like put your data in cell AG, then use the empty columns to work with it? also, it looks like your original date is Text. I'm assuming the original date is text.

'	'	'	'	'	'	'	'
'assume your data is in cell AG17 '06-12-2024	'	'	'	'	concatenate as text	format	format
06-12-2024	06	06-12	12	2024	12/06/2024	12/6/2024​	6-Dec-24​
	=LEFT(AG17,2)	=LEFT(AG17,5)	=RIGHT(AI17,2)	=RIGHT(AG17,4)	=AJ17&"/"&AH17&"/"&AK17	=DATEVALUE(AL17)	=AM17

Dear @bobaol ,
the original data stored into column M is DATE. I've formatted the cells to Date and it also carries Data Validation to enter date in DD/MM/YYYY format.

I understad your suggestion...but this seems bit tedious to add in the macro?

Any other method suggestable?

TIA

 

[MARK858]

Maybe (only tested for one cell being amended, I would only test for the one cell at a time being changed but I've left it in there)

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 = 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, 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 = Cells(cell.Row, "G")
              
                ' If the existing list is not empty, append a comma and space
                If existingList = "" Then
                    existingList = CStr(cell.Value2)
                Else
                    If Right(existingList, 2) <> ", " Then
                        existingList = existingList & ", " & CDate(cell.Value)
                    Else
                        existingList = existingList & CDate(cell.Value)
                    End If
                End If
              
                ' 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
End Sub

 

[Akuini]

the original data stored into column M is DATE. I've formatted the cells to Date and it also carries Data Validation to enter date in DD/MM/YYYY format.

1. What's the data validation formula?
2. What is your windows date setting, dmy or mdy?

Again, try formatting the cells in col M as "dd/mm/yyyy", instead of just Date. You can do it manually by Format Cells>Number>Custom>in Type: dd/mm/yyyy
You can also format the cells by macro, like this:

Range("M5:M100").NumberFormat = "dd/mm/yyyy"

 

[MARK858]

Please note that I have made an amendment to the code in post 6

 

[Alex Blakenburg]

This happens only at the first entry.

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

 

[Gajendran Yadhav]

1. What's the data validation formula?
2. What is your windows date setting, dmy or mdy?

Again, try formatting the cells in col M as "dd/mm/yyyy", instead of just Date. You can do it manually by Format Cells>Number>Custom>in Type: dd/mm/yyyy
You can also format the cells by macro, like this:

Range("M5:M100").NumberFormat = "dd/mm/yyyy"

Hi @Akuini

It is long time we had interacted... Hope you are doing great.

1. What's the data validation formula?
Data -> Data Validation -> (in Allow) Date ; (Data) greater than ; (Start date) 31-12-1999

2. What is your windows date setting, dmy or mdy?
Windows setting is DD/MM/YYYY.