Excel VBA Userform Date issue

Im really frustrated with your responses to be honest because they do not assist in any way with my problem 

Well, I don't understand why you don't want to understand how Excel works.

Perhaps it is helpful if we both take a step back and take a look at how Excel works, without any text conversation.

A date in Excel is a number, if we want to store a date into a cell, we have to store a number.

Make a new file and execute this code:

Sub Test()

  Dim i As Long

  For i = 1 To 30

    With Range("A" & i)

      .Value = 54321

      .NumberFormat = "[$-" & i & "]ddd dd/mmm/yyyy"

    End With

  Next

End Sub

You can format the cells in A1 with any date format you like, works.

In an exaggerated example, I can show what I'm saying and what you're trying, please execute that:

Sub Test2()

  'What I suggest

  With Range("C1")

    .Value = 54321

    .NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"

  End With

  'What you try

  Range("C2") = "Sonntag, 20. September 2048"

End Sub

You try to store a date as text (in a slightly different way using FORMAT) and that did not work.

C2 is a text, C1 is a real date.

If you write a text into a cell that looks like a date, Excel tries to convert the text into a number:

Sub Test3()

  'What you try in fact:

  Range("E1") = "20/02/03"

  Range("E2") = "01/20/03"

  Range("E3") = "01/02/30"

End Sub

It depends on the regional Office settings and/or the local PC what's the result:

My local format is dd.mm.yyyy

As you see, on my PC only E2 and E3 are converted into a real date, the conversation failed with E1 and Excel stores a text. That looks a bit curious because 20. Feb. 2003 is a valid date...

But that's the way how Excel works. And that is also the situation in your database, text and dates are mixed up.

You can't use the FORMAT function to store a date into a cell. That's the point. Either you understand that or I can't help you.

Andreas.

Hi Andreas

Thank you again - but please I dont have any issue with how Excel works regarding the date/number /text issue

What I am asking again is the issue of how to format the VBA USER form textbox as a number for excel to acknowledge as a date 

as before

ongoing root problem - the fact that user input data is being saved onto a worksheet in what the user sees as the correct date format eg 03/10/2020 but on the worksheet it is saved as 10/03/2020 and that is what is returned to the user form should the entry be recalled for editing at any later point in time - If the worksheet is edited directly to change the date into the correct format - 03/10/2020 - that is exactly what is returned, in the correct format - simple

What I am describing is that i know that the TXT box values are text - I do not want text entered into the worksheet from the user form - thats what I am asking

I have tried all of the formatting you have described but it still fails when i return the data back into the user form for later/additional formating

The other issue is that the worksheet cells are not fixed but dynamic so that a new user form entry adds a new record on a new line - so please

can you help me how to format the userform and worksheet to allow seamless transfer back and forward between the worksheet and user form

I will send you the latest version to look at what I mean but you may need to set arabic as unicode to get it to work 

thank you

ongoing root problem - the fact that user input data is being saved onto a worksheet in what the user sees as the correct date format eg 03/10/2020 but on the worksheet it is saved as 10/03/2020 and that is what is returned to the user form should the entry be recalled for editing at any later point in time - If the worksheet is edited directly to change the date into the correct format - 03/10/2020 - that is exactly what is returned, in the correct format - simple

We are spinning in a circle.

Try Userform2 in this file to add dates:

https://www.dropbox.com/s/s2ox98stgxnc76j/1c095b61-b8e8-4395-9b15-5a5419832828.xlsm?dl=1

Andreas.

version4.5

Hi andreas - well thank you for the file with user forms - unfortunately in your userform 1 - you have the same issue - in the link above Ive attached some screen shots of your form to show what you what is happening 

it shows in the first screenshot that I inputted 03/11/2020 on the user form- you also see that on saving the worksheet records 11/03/2020 -  this format is then returned as 11/03/2020 when called back into the user form -i tried again with 05/12/2020 and you can see that the same issue exists - which has been always query - why does this happen and how do we fix it 

Ive also added screen shots from my user form showing the same problem - follow from the user input, the addition to the worksheet and user form list box and finally returning the data back into the user form for editing

I have also attached the latest version 4.5 of my code 

I hope this now demonstrates to you the problem

Thank you for your help - hopefully we can resolve this yet

Adrian

well thank you for the file with user forms - unfortunately in your userform 1 - you have the same issue - in the link above Ive attached some screen shots of your form to show what you what is happening

If you try Userform2 you can see the interpreted date from Textbox2 within Textbox3 on the form before the date is written into the sheet in your local system settings.

That real date is written into the sheet, and you get the real date back later. There is no difference or exchange in day / month or between the form and the sheet.

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.

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.

The point is you can not solve that in the way you try, this does never work, resp. has worked, it's an illusion:

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

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

That's what I'm talking about all the time.

Andreas.