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

Gajendran Yadhav

Board Regular
Joined
Sep 8, 2023
Messages
51
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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..
VBA Code:
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:
1710748313025.png

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

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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try formatting the cells in col M as "dd/mm/yyyy".
 
Upvote 0
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 textformatformat
06-12-20240606-1212202412/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
 
Upvote 0
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 textformatformat
06-12-20240606-1212202412/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
 
Upvote 0
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)

VBA Code:
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
 
Last edited:
Upvote 0
Solution
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:
VBA Code:
Range("M5:M100").NumberFormat = "dd/mm/yyyy"
 
Upvote 0
Please note that I have made an amendment to the code in post 6
 
Upvote 0
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
Rich (BB code):
                ' 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
 
Last edited:
Upvote 0
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:
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,445
Messages
6,172,177
Members
452,446
Latest member
walkman99

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