Difference between two dates 1

[slawson7]

I have the following code:

Sub datefunctions()

   rightMostColumn = Range("A4").End(xlToRight).Column

   Set myRange = Worksheets("temp").Range(Cells(4, 1), Cells(4, rightMostColumn))

   mindate = Format(Application.WorksheetFunction.Min(myRange), "yyyymmdd")

   For i = 1 To rightMostColumn
      thisDate = Format(Cells(4, i), "yyyymmdd")
      MsgBox (mindate & " " & thisDate)
      Cells(5, i) = DateDiff("d", mindate, thisDate)
   Next i

End Sub

I want the script to find the lowest date value in a row of dates, then, for each date value, write in the cell below it the difference (in days) between it and the minimum date.

The DateDiff line of code throws a "type mismatch" error when I do this. Both row 4 & 5 are formatted with identical date formats. Could someone please tell me why I get this error, while I've still got some hair left?

TIA

 

[PHV]

What about this ?
Sub datefunctions()
Dim mindate As Date, thisDate As Date
rightMostColumn = Range("A4").End(xlToRight).Column
Set myRange = Worksheets("temp").Range(Cells(4, 1), Cells(4, rightMostColumn))
mindate = Application.WorksheetFunction.Min(myRange)
For i = 1 To rightMostColumn
thisDate = Cells(4, i)
MsgBox (mindate & " " & thisDate)
Cells(5, i) = DateDiff("d", mindate, thisDate)
Next i
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[duGly]

I like PHV's solution.

TIA, the reason you were getting an error code is because the DateDiff function requires date values, while you were passing it strings (minDate and thisDate). Actually, since you did not explicitly dim minDate and thisDate, they were, by default, variants. But when you assigned their values from the worksheet, they took on string format.

The CDate function can convert the strings to dates, but I still like PHV's solution best for the following reasons: 1) minDate and thisDate are explicitly dimmed as dates, and 2) they obtain their date values directly from the worksheet without any type of conversion.

[purple]— Artificial intelligence is no match for natural stupidity.[/purple]

 

[slawson7]

Muchos kudos to PHV, and thanks to duGly for backing it up. Worked brilliantly.