Excel VBA Userform Date issue

Hello again Adrian,

I opted out of this thread because I did not want you to become confused by different methods adopted by Andreas and myself. However, as it appears that you do not yet have a solution to your problem of writing dates from text in VBA to the worksheet I thought that I should opt back in and perform a test to establish if the method by Andreas of converting the text to dates is the problem or some other problem exists.

I have uploaded a workbook to the link below as a zipped file. Download and extract the workbook and test. Insert dates and times into the textboxes in the d/m/y format and see what result you get after clicking the "Calculate Time Difference" and it writes the dates and times back to the worksheet.

You will see that I have used the Split Function and the DateSerial function to convert the text dates to real dates. I am still dubious about the use of CDate but the method I have used performs a definite conversion based on the text date in d/m/y/ format.

Note the code to convert the time difference date to text for the time difference textbox. It is necessary to use the worksheet.text function because VBA does not work with retaining hours when the time difference is greater than 24 hours.

I have not attempted to use your method of assigning to the ListBox and then writing all to the worksheet because that is not required for this simple test.

If the test does not work then I believe there is a problem with VBA and dates when converting from text to date format and it could be applicable to different regions even though we both use d/m/y format. However, if it does work then I believe that CDate is not working as intended in your regional setup.

Link to the workbook: 

https://1drv.ms/u/s!ArAXPS2RpafCsxEa9AyUHcRu6EaF?e=9XSKhL

Hi Andreas

I really wish I could speak german

Please please follow slowly what im trying to say to you

1/ There is no special date format regional or otherwise needed - its simply UK - dd/mm/yyyy that is the regional setting/ cell format for my PC and excel - nothing complicated

2/ I do not want to do anything fancy - other than have the user fill in the relevant dates as per the userform questions - that being the date of the call and the date the incident finished only

(I dont want to touch anything regarding the 'now' date or time - that is irrelevant to this issue and it works and even gives the correct formatting!!)

The calculations on the spreadsheet in all respects work fine - EXCEPT where the date format has been reformatted from dd/mm/yyyy to mm/dd/yyyyy which then distorts the elapsed time by over one month 

3/ the date format needs to be dd/mm/yyyy - approximately the same as germany!! - currently, if - as you say you enter the date as dd/mm/yyyy into the USERFORM text boxes ( as text or otherwise  - ok I am aware of that !!!) excel on the worksheet - if the day is between 01 - 10 changes the date to MM/dd/yyyy where the day is < the 10th day of the month !! and that is the problem!!! 

4/ I dont understand why it is necessary to 'parsedate' anything, as the date format is a standard format ??? I do not want to run any un-nccessary functions for such a simple operation

5/ So again -PLEASE !! I need to know how to set the 

.Cells(irow, 6) = frmincident.txtdatefinished.Value to be formatted as a date (dd/mm/yyyy)

so that the text or 'Value' is passed to excel as a date - simple and as you keep saying!!. The list box entry should now show the same as the worksheet entry in the format dd/mm/yyyy WITHOUT swapping the day/month around - this has been the root problem

6/ Then when the data gets called back into the Userform by the user selecting an entry from the list box and pressing edit -

using

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

I actually think that this is working ok because what is with in the worksheet for the 'Value' comes into the List box in the same way and is the original guidance from OssieMac

the 'txtdate' and 'txtfinished' box should now show the value in the box as dd/mm/yyyy 

-Please now does that make sense ?

7/ I just need to know what code to write - that is the expressions/operators to add to the above lines to get subroutine to save the .values to the spreadsheet in whatever is required - nothing to do with wanting to use text or what excel does - Im happy that I understand what excel does when a text string appears as if it is a date 

8/ The issue is on the user form not the spread sheet !! Correct?

Please please lets finish this simply without talking around how excel looks at text and dates 

Regards

Adrian

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