Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Difference between two dates 1

Status
Not open for further replies.

slawson7

Technical User
Apr 23, 2007
7
GB
I have the following code:

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
 
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
 
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][ponder]— Artificial intelligence is no match for natural stupidity.[/purple]
 
Muchos kudos to PHV, and thanks to duGly for backing it up. Worked brilliantly.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top