Why does the min function gives me different values?

[chonskisa]

Hey'all,
Slowly but surely loosing my mind.

On the left, the values copied from my calculation file. The min between 31 may and 8 junes gives me 8june...
The same formule, the same dates but this times typed manually and not copied from my file, give me different (correct) value.
What the hell is going on?

THanks for any kind of help...

 

[Fluff]

Hi & welcome to MrExcel.
I suspect that the value in A2 is text & not a date. If you format the cell as General what do you see?

 

[RoryA]

I suspect the value in A2 is text, not a real date. Is it left aligned in the cell by default?

 

[Joe4]

Welcome to the Board!

It looks like the value in cell A2 is entered as text (notice how it is left-justified? dates and numbers are usually right-justified).
You need to convert it to a valid date in order to do date math on it.

Note it is possible that your spreadsheet or your Excel regional settings are set up do have dates entered in MONTH/DAY/YEAR format instead of DAY/MONTH/YEAR, and that is why that one was converted to text. The other two may not be, because they might be seen as valid dates, but not the dates you expect. Excel might see those as August 6, not June 8.
One easy way to tell is to enter this formula in any blank cell and see if it returns JUNE or AUGUST.

=TEXT(A3,"mmmm")

 

[chonskisa]

Hi and thanks for your answers.
Y'all are right, it is indeed recognized as "text" when I change the format to General.

Is there a way I can change the format of this cell (the 31/05/2023)?
Just for the record, this cell is fed by a Date picker. I'm changing the format of the cell to Date but nothing happens.

Thanks a lot

 

[chonskisa]

Oh and when I check in the VBA editor I see this for the DatePicker :

Private Sub DTPicker2_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Should I change something inhere?

 

[Fluff]

That code doesn't do anything, you will need to post the code that actually enters the date into the cell.

 

[chonskisa]

There is no code.
The only thing that happens is that I added a DatePicker on my sheet and adapted on the properties of the DatePicker that the value should be stored in D3. The only thing is that the value stored is text and not date...

 

[Fluff]

I don't have the datepicker control, so cannot help any further.