PDA

View Full Version : [SOLVED] Calculating number of days between 2 dates



Chunk
05-19-2017, 06:32 AM
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
05-19-2017, 06:46 AM
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
05-19-2017, 06:56 AM
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
05-19-2017, 07:10 AM
19219


No merged cells. Here is a sample workbook

mdmackillop
05-19-2017, 07:34 AM
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
05-19-2017, 02:07 PM
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
05-23-2017, 09:19 AM
And there it is. After ensuring that the columns were formatted as a date, everything works fine. Thank you for your time and help