Calculating number of days between 2 dates

[Chunk]

Good morning all,

I am trying to get a routine that will loop through column "L", and if the cell is NOT empty ( it will have a date, mm/dd/yyyy) calculate the difference between that date and a date in column "A" in days, and populate column "M" of the same row with the product.
Here is the code that I have (that is giving me a type mismatch error).

Sub Initial_BD_Dates()
    Dim rng As Range
    Dim i As Long
    'Set the range in column L you want to loop through
    Set rng = Range("L3:L175")
    For Each cell In rng
        'test if cell is empty
        If cell.Value <> "" Then
            'write to adjacent cell
            cell.Offset(0, 1).Value = cell.Offset(0, 0).Value - cell.Offset(0, -11).Value
        End If
    Next
End Sub

Any ideas? Thanks in advance.

Chunk

[mdmackillop]

I don't see an issue with your code. Are you using merged cells? Please post a sample workbook.
Only suggestion, but may not be required
If cell.Value <> "" And cell.Offset(0, -11) <> "" Then

[Paul_Hossler]

1. You can check to make sure that they really are dates

2. Attach a small sample workbook that shows the issue will make it easier

3. An alternative to .Offset that I find easier to read

Option Explicit
Sub Initial_BD_Dates()

    Dim rng As Range, cl As Range
     
     'Set the range in column L you want to loop through
    Set rng = Range("L3:L175")
    For Each cl In rng.Cells
         With cl.EntireRow
            If Len(.Cells(12).Value) > 0 Then
                If IsDate(.Cells(12).Value) And IsDate(.Cells(1).Value) Then
                    .Cells(13).Value = .Cells(12).Value - .Cells(1).Value
                End If
            End If
        End With
    Next
End Sub

[Chunk]

Sample.xlsb


No merged cells. Here is a sample workbook

[mdmackillop]

Your formatting of A-I is General, not Date. Only Column L which is date formatted, shows the date correctly.

In a cell below, enter =A3*1 and copy across to verify

[Paul_Hossler]

Just 'cuz something looks like a Date, doesn't mean it is

I added CDate()'s to my first thought above

Option Explicit

Sub Initial_BD_Dates()
     
    Dim rng As Range, cl As Range
     
     'Set the range in column L you want to loop through
    Set rng = Range("L3:L175")
    For Each cl In rng.Cells
        With cl.EntireRow
            If Len(.Cells(12).Value) > 0 Then
                If IsDate(.Cells(12).Value) And IsDate(.Cells(1).Value) Then
                    .Cells(13).Value = CDate(.Cells(12).Value) - CDate(.Cells(1).Value)
                End If
            End If
        End With
    Next
End Sub

[Chunk]

And there it is. After ensuring that the columns were formatted as a date, everything works fine. Thank you for your time and help