Disclaimer: This is a fairly specific bug I think I ran into in Excel but it's weird enough that I'll never find an answer to fix it, if it's even possible, if I don't post it here. It is an interesting one though so bear with me.
So a little context, I wrote some code that lets me capture a time stamp in column A when I input into column B. That works fine. I added a bit so it prompts the user if they are sure they want to change an entry after it has been entered, to keep the integrity of the time stamp. This also works.
During testing I ran into an issue, however. If I simply type a number into col A, the number 5 for example, it forces it into the format 1/5/1900 00:00. Makes sense to me, as Excel clearly chose 1900 as an arbitrary starting point. But if I try to edit this date, the prompt asking me if I want to change it shows up as 1/4/1900 (screenshot below). For the longest time I couldn't figure out why it was off by 1. I realized that any input between 1 and 60 (dates jan 1 to feb 29) caused this problem. Any dates after 3/1/1900 prompted me correctly. I did some digging and I found that the year 1900 doesn't have a leap year, so the number 60 should resolve to 3/1/1900 but instead goes to 2/29/1900 in Excel.
I know it's a small issue and only affects 60 possible entries in column A, but I feel helpless to fix it as it seems that the problem is how Excel calculates the leap year in 1900 when it shouldn't. Does anyone have a workaround, or at least a confirmation that this is a problem with Excel and not my code?
Screenshot of 3 cases of editing column A.
The 1st and 3rd results are expected. The middle one shows that even though the previous field was actually Jan 5, the prompt shows up as Jan 4. I'll post the code I have below in case anyone wants to try the same thing.
Private lastVal As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim timeCol, taskCol As Integer
Dim sRow, sCol As Integer
Dim currentCell As Range
timeCol = 1
taskCol = 2
sRow = Target.row
sCol = Target.Column
Set currentCell = Cells(sRow, sCol)
'Checks for timestamp or task column. If Cell wasn't empty, prompt user to
'confirm the change
If sCol = timeCol Or sCol = taskCol Then
If lastVal <> "" Then
response = MsgBox("Old Value: " & lastVal & Chr(10) & Chr(10) _
& "New Value: " & Cells(sRow, sCol) & Chr(10) & Chr(10) _
& "Are you sure you want to make this change?", _
vbYesNo + vbQuestion, "Change Confirmation")
If response = vbYes Then
'Do nothing
Else
'Reject the change
Application.EnableEvents = False
currentCell = lastVal
Application.EnableEvents = True
End If
End If
End If
'Checks for task column. If timestamp cell at current row is empty
'and something was entered into task column, fills timestamp cell
If sCol = taskCol Then
If Cells(sRow, timeCol) = "" = True Then
If currentCell <> "" Then
Application.EnableEvents = False
Cells(sRow, timeCol) = Now()
Application.EnableEvents = True
End If
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sRow, sCol
Dim currentCell As Range
sRow = Target.row
sCol = Target.Column
Set currentCell = Cells(sRow, sCol)
lastVal = currentCell
End Sub
