Dealing with label dates in the dd-mmm-yy format (CDate reverses 'yy' and 'dd')

[RawlinsCross]

So I have a userform label with the caption of a date in the format "dd-mmm-yy" - let's just say it's "14-Oct-21". I want to search (.Find) this date in column A with dates also in the format "dd-mmm-yy". Now I realize in column A that I actually have dates (i.e. when I click on the corresponding cell in column A I see in the formula bar 2021-10-14.

So I think I have to convert the string label date "14-Oct-21" to the 2021-10-14 in order to find it. But when I try to convert the string label date it converts it to 2014-10-21.

I find dates in excel to be quite frustrating particularly when coupled with the .Find function. Any thoughts on how to proceed here?

John

 

[jasonb75]

If you're seeing the date in the formula bar as 2021-10-14 then it looks like that is a text string, in which case converting your caption date to a proper date will not work.

In simple a simple test this works correctly, see if you can use it to fix what you have.

Sub test()
Dim d As Date
d = "14-Oct-21"
ActiveSheet.Cells.Find(Format(d, "yyyy-mm-dd")).Select
End Sub

 

[dmt32]

Assuming that the dates in your range are real dates, you will need to coerce the Label Caption to a date using type conversion function like CDate. You will also need to use the LookIn:=xlFormulas argument which searches the Formula of cells and is not affected by cell formatting like Dates.

To return the format of the found range to your control (TextBox?) you use the Range.Text property – this should return the text of the cell (what you see) and not its underlying value.



Example code

Dim FoundCell   As Range
Dim Search      As Variant

Search = Me.Label1.Caption
If IsDate(Search) Then Search = CDate(Search)

Set FoundCell = Worksheets("Sheet1").Columns(1).Find(What:=Search, LookIn:=xlFormulas, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then
 
 'use the Range.Text property to return what you see in the cell
 '(formatted date) & not its underlying value.
 MsgBox FoundCell.Text

Else
    MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
End If

Dave

 

[RawlinsCross]

Hey there... my apologies for the late reply, haven't been in front of a computer for weeks (which definitely is a good thing sometimes!). I appreciate the replies and some good take-aways. Thanks!

 

[dmt32]

Most welcome & hopefully, one of solutions offered will work for you if not post back.

Dave