Excel VBA Userform Date issue

you are totally confusing me

Why can we not do something here  to these lines???

Aahh, good news, if I confuse you, then we come closer. :-)

I think I can now repeat something I have already said with a few more details:

If you write a text that looks like a date into a cell (the****cell format, except text, doesn't matter!) Excel tries to interpret the text as date, same way as CDate or DateValue does.

Furthermore: If you have a text that looks like a date (or time!) in a cell and you use a formula to calculate with that cell, Excel tries to interpret the text as date... same problem.

(I only say CDate in the following, you can replace it with DateValue or Excel)

And this date conversion has a few tricks in store, if you pass an incomplete or ambiguous date strings to the routine, it guesses which date you like, in respect to the local system settings.

An example:

Sub Test()

  Range("A1") = CDate("14/1")

  Range("A2") = CDate("1/14")

End Sub

Most systems in the world have DMY or MDY order, my is DMY, I don't know what your has...

Further details about the orders, have a look here at xlDateOrder:

https://docs.microsoft.com/en-ie/office/vba/api/excel.application.international

The conversation knows the first 2 parts are the day and the month. The 3rd part, the year, is missing. Therefore CDate uses the current year to calculate the real date (number).

A 14th month doesn't exists in the world, that means both dates are the 14 Jan 2020!

CDate assumes a typo and exchanges the day/month order.

And this happens every time when you write a text that looks like a date into a cell! No exception!

Sub Test()

  Range("A1") = CDate("14/1")

  Range("A2") = CDate("1/14")

  Range("A3") = "14/1"

  Range("A4") = "1/14"

End Sub

The problem arises when it is not clear what is the month or the day:

Sub Test()

  Range("A1") = CDate("12/1")

  Range("A2") = CDate("1/12")

  Range("A3") = "12/1"

  Range("A4") = "1/12"

End Sub

On my DMY system A1 is the 12 Jan 2020 and A2 is the 1 Dec 2020

On a MDY system the opposite is the case: A1 is the 1 Dec 2020 and A2 is the 12 Jan 2020

The 2 points where you struggle are:

a) The cell format doesn't matter

b) Never write a text that looks like a date into a cell

EDIT: Write the real date into the cell and the issue is gone, e.g.

  .Cells(irow, 6) = ParseDate(frmincident.txtdatefinished)

Andreas.

Hi Andreas

While I really do appreciate your efforts - you are totally confusing me - I dont understand why the above code involves inputting the Now /current time ? that has never been an issue and you are moving away again from what I need youre help with - this code

.Cells(irow, 23) = [Text(Now(),"DD-MM-YYYY HH:MM:SS")]

Works well and has no issues in transferring the correct date format back and forward -it is automatically added and is not a user input - it has nothing to do with the user form inputs and is an audit against who and when submitted /edited the form - i never mentioned this a problem and you can see in the data base and in the list box that it resents the date and time in the correct format ?

Why can I not format the the other date entries in columns F and S in the work book - txtdate and txtfinished - they are the only two im having the issue with 

Why cant we simply format the text box value as a date? without being subject to a function?

my question is

1/ where do I add this formatting in the vba - module or Form and how do I do that

Surely this is simply about using the correct expression to convert the value?

2/ Why cant we use 

  .Cells(irow, 6) = frmincident.txtdatefinished.Value = xxxxxxxxxx etc?????

  .Cells(irow, 19) = frmincident.txtdate.Value= xxxxxxxxxx etc?????

and or 

    Me.txtdatefinished.Value = Format(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 5), "dd/mm/yyyy")

    Me.txtdate.Value = Format(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 18), "dd/mm/yyyy")

Why can we not do something here  to these lines???

If you want to enter the dates in your Userform in a different format, e.g. dd/mm/yyyy, you have to write your own text/date parser to calculate the real date number from that text. We already said that 20 posts before.

I understand that and I tried that in the various formats - however Im not convinced I am writing the script under -

Sub Test()

  Dim S As String

  S = "3/11"

  MsgBox ParseDate(S)

End Sub

Function ParseDate(ByVal Expression As String) As Date

  'Parse Expression as date in the format dd/mm/yyyy

  Dim Digit As String

  Dim Part(0 To 2) As String

  Dim i As Long, j As Long

  'From left to right

  For i = 1 To Len(Expression)

    'Get a char

    Digit = Mid$(Expression, i, 1)

    'Is it a number?

    If Digit Like "#" Then

      'Yes, store

      Part(j) = Part(j) & Digit

    Else

      'Next field

      j = j + 1

      If j > 2 Then Exit For

    End If

  Next

  'Check fields

  For i = 0 To 2

    'Empty field?

    If Part(i) = "" Then

      'Fill the missing parts from current date

      Select Case i

        Case 0: Part(i) = Day(Now)

        Case 1: Part(i) = Month(Now)

        Case 2: Part(i) = Year(Now)

      End Select

    End If

  Next

  'Build the date

  ParseDate = DateSerial(Part(2), Part(1), Part(0))

End Function

Hello Andreas

One thing I agree with you on is that we are going around in circles 

If your local settings are mm/dd/yyyy you have to enter the dates as mm/dd/yyyy into the form. If you do so, there are no issues. That is the basic point that you must accept / understand, Excel works that way.

I understand what you are saying - however as ossiemac mentioned to you about using Cdate- my regional and systems settings are UK - dd/mm/yyyy - this is what the relevant cells are also formatted to and all the time relevant cells are set s custom hh:mm ( i dont have any problem with the time issues by the way!) I dont know how this becomes text other than the formatting of the user form text boxes - and that is where my request for help is please

If you want to enter the dates in your Userform in a different format, e.g. dd/mm/yyyy, you have to write your own text/date parser to calculate the real date number from that text. We already said that 20 posts before.

I understand that and I tried that in the various formats - however Im not convinced I am writing the script under -

1/ the correct locatio ie module or form

2/ I get errors on the expressions etc

Your database already contains text and dates mixed up, there is no chance to solve that with your Userform.

The entries are dummy data from ongoing versions - dont worry about this i just didnt clear out the old entries as I changed the VBA code as per your guidance - so yes it is all mixed up but thats not relevant - I just need to reset the code correctly as per your help 

So please can you just tell me what I need to write and where to resolve the user form issues

Than you again

Adrian

version4.5

Execute the code below in your file, as you see most but not all of you dates / times are text and many of them are also invalid dates.

Andreas.

Sub InvalidDates()

  Dim R As Range

  For Each R In Intersect(Range("3:28"), Range("E:L,S:T,W:W"))

    If VarType(R.Value) <> vbDate Then

      With R.Borders(xlDiagonalDown)

        .LineStyle = xlContinuous

        .Color = vbRed

        .Weight = xlThick

      End With

    End If

  Next

End Sub