Format() not formatting according to parameter

[TorontoJim]

I need help figuring out why I can't force dates to compare in the way that I want.

I have worksheet and want to compare a date from a datePicker control to a date in a column to find a match. The column in the worksheet is given the Custom format "yyyy-mm-dd". I did not select the option that responds to regional settings (although I did try that on the sheet while testing this) because I work on this at home and at the office. Office uses Canadian date format (as given) but home computer uses US date format (mm-dd-yyyy).

The value selected in the datePicker control is March 5th, 2009. There IS a record in the worksheet that has the date 2009-03-05.

I have tried many different combinations from raw values to formatting to raw value vs. formatted etc. (for the last three hours). I've even created a workbook specifically for the purpose of testing dates and times (another story all together).

This is the function I created to lookup the record. Simple really. It loops through the worksheet and finds the record that matches two paramters (first is a string, no problem there). The second is the date. Shown below is what to me is the most mind boggling aspect of the whole thing. I have it comparing the FORMATTED output of both the datePicker (yyyy/mm/dd) and the record (yyyy/mm/dd)

Public Function LookupRecord(ByVal strPlatoon As String, ByVal dateReportDate As Date) As Long
    LookupRecord = 0
    Dim longLastRow As Long
    longLastRow = FindLastRow_data
    Dim i As Long
    For i = longLastRow To 2 Step -1
        If ThisWorkbook.Worksheets("Data").Cells(i, 5).Value = strPlatoon Then
Debug.Print ("comparing " & Format(ThisWorkbook.Worksheets("Data").Cells(i, 4), "yyyy/mm/dd") & " to " & Format(dateReportDate, "yyyy/mm/dd"))
            If Format(ThisWorkbook.Worksheets("Data").Cells(i, 4), "yyyy/mm/dd") = Format(dateReportDate, "yyyy/mm/dd") Then
                LookupRecord = i
                Exit For
            End If
        End If
    Next i
End Function

The Debug statement puts out this:

comparing 2009/03/05 to 2009/05/03

You can see in the first one that the cell value is formatting according to my format statement HOWEVER the dataPicker value
is Formatting to yyyy/DD/mm instead of yyyy/mm/dd.

I did try changing dateReportDate to a Variant:

Public Function LookupRecord(ByVal strPlatoon As String, ByVal dateReportDate As Variant) As Long

...but this had the exact same effect.

Can someone PLEASE tell me why this is happening or what I am doing wrong there? This is pretty simple stuff. It's not rocket science. I'm just missing why the
Format() is not doing what I'm telling it to do.

Your time is appreciated,
Jim

 

[Jonmo1]

It may have something to do with your worksheet being in UK date mode, but the Vba Date is in US format..

Try this

Format(cDate(dateReportDate), "yyyy/mm/dd"))

 

[Norie]

Jim

Try not using Format.

Dates in Excel are stored as long integers eg 24th April 2009 <> 39927.

Formatting a date only does that - format, it doesn't change the underlying value.

 

[TorontoJim]

SOLVED: Format() not formatting according to parameter

I think this was a case of not seeing the forest for the trees.

I mispoke about this, the user input was not coming from a datePicker control, it was coming from a listbox control. The first column (the .Value) was the date and was formatted mm/dd/yyyy. I believe that is why I was not able to make it format the way I wanted to because it wasn't, in actuality, a date. It was just a string.

I changed the code that loads the date in the ListBox to format the date yyyy/mm/dd. As soon as I did that, WITH the worksheets column set to the date freld that recognizes regional settings, it worked just fine.

It was from your comments that I started looking at other things so I credit you both with helping solve this.

Thank you again for you time,

"Sheepishly checking his other listboxes..."
Jim