Hi,
I have a spreadsheet where I need to keep track of different tasks that need to be completed where I work. The list has a task name column, who has action, what the action is, date the action when active and a days active column. I need to use VBA to calculate the number of days active because as you will see the in attached test file with dummy data, sometimes the task will have no current action (i.e. listed as N/A), one action (i.e. single current action and current action start date), or multiple actions (i.e. two or more current actions and current action start dates). I have gotten the code below which is also included in the attached file to work for the N/A case since the code simply just checks to see if the date column says "N/A" and if yes, assigns "N/A" to the days active column in that row. When I attempt to calculate the number of days active for rows which have one or more dates, I get a type mismatch error and I'm not sure why.
Sub DateTest()
'calculates the number of days a current action has been active based on today's date and the date provided in the Current Action Start column (column D)
lastrow = Range("A" & Rows.Count).End(xlUp).Row 'find last row which contains any data
Dim TodayDate As String
TodayDate = Date 'what is today's date? used for determining number of days active
For j = 2 To lastrow 'iterate through all rows with data to determind days active
If Range("D" & j).Value = "N/A" Then 'if no current action is active
Range("E" & j).Value = "N/A"
Else 'if there is at least one date in the Current Action Start column
datestr = Split(Range("D" & j).Value, Chr(10)) 'create an array of all dates in current action start date column for current row
For k = LBound(datestr) To UBound(datestr) 'iterate through DateStr array
DateVal = TodayDate - DateSerial(Year(datestr(k)), Month(datestr(k)), Day(datestr(k)))
DaysActive = DaysActive & Chr(10) & DateVal
Next k
Range("E" & j).Value = DaysActive
End If
Next j
End Sub