CODEWITHSUNDEEP
vbaexcel
splucena
splucena

Reputation: 383

Excel VBA date is being subtracted by one day

Private Sub cboMonth1_Change()
    Dim rng As Range
    Dim rngStart As Range
    Dim rngEnd As Range
    Dim dt As Worksheet
    Dim strStart As String
    Dim strEnd As String
    Dim ICell As Range

    Set dt = Worksheets("Sheet1")

    If Me.cboMonth1.Value = "January" Then
        Set rng = Range("J2")
    ElseIf Me.cboMonth1.Value = "February" Then
        Set rng = Range("AO2")
    End If

    If rng.MergeCells Then
        Set rng = rng.MergeArea
        Set rngStart = rng.Cells(1, 1)
        Set rngEnd = rng.Cells(rng.Rows.Count, rng.Columns.Count)

        strStart = Mid(rngStart.Address, 1, Len(rngStart.Address) - 1)
        strEnd = Mid(rngEnd.Address, 1, Len(rngEnd.Address) - 1)

        Me.cboStartDate.Clear

        For Each ICell In dt.Range(strStart & "3 : " & strEnd & "3")
            Me.cboStartDate.AddItem (ICell.Value)
        Next ICell

    Else
        MsgBox ("Not merge cells")
    End If
End Sub

Cell Format: Custom dd/mm

Example: In cell J3 it contains 1/1/1900 but J3 value displays 12/31/1899 in combobox the last item would display 30/1/1900. I'm expecting that combobox would contain dates from 1/1/1900 - 31/1/1900 if January is selected and so on.

I have tried printing individual values MsgBox (dt.Range("J3").Value) but it gives me 12/31/1899 but the content of that cell is 1/1/1900.

*Note I don't have any code that will alter the contents of cells.

Upvotes: 2

Views: 821

Answers (1)

Comintern
Comintern

Reputation: 22185

The issue is how VBA stores dates and times - they are actually Doubles with the Integer portion as the number of days since 12/31/1899. The problem is that it simply doesn't work correctly for days '0' and '1'. I'm not sure if the issue is floating point precision or something gets lost in the Excel-VBA transition, but you can see the effect with the code below:

Debug.Print Format(CDate(0), "dd/mm/yyyy") 'Outputs 12/30/1899
Debug.Print Format(CDate(1), "dd/mm/yyyy") 'Outputs 12/30/1899
Debug.Print Format(CDate(2), "dd/mm/yyyy") 'Outputs 01/01/1900
Debug.Print Format(CDate(3), "dd/mm/yyyy") 'Outputs 01/02/1900

The solution (albeit obnoxious) is to use the Text of the cell and cast it to a date (unless you just need the string, in which case don't cast it at all):

For Each ICell In dt.Range(strStart & "3 : " & strEnd & "3")
    Me.cboStartDate.AddItem CDate(ICell.Text)
Next ICell

More wierdness demonstration code with the irreversibility of date casts before 1/3/1900:

Debug.Print CDate(ActiveSheet.Range("J3").Text)  'Outputs 01/01/1900
Debug.Print CDate(ActiveSheet.Range("J3").Value) 'Outputs 12/30/1899
Debug.Print CDbl(CDate(ActiveSheet.Range("J3").Text))  'Outputs 2
Debug.Print CDbl(CDate(ActiveSheet.Range("J3").Value))  'Outputs 1

Upvotes: 5

Related Questions