Excel VBA Userform Date issue

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

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.

Andreas

Im really not convinced you are both not reading my posting correctly and have not looked at my vba scripts - your last post does not assist me at all - what was the point of those links please? I am well aware of the formatting and have tried all combinations without any success = and AGAIN the worksheet is not formatted as TEXT !!!! 

Im really frustrated with your responses to be honest because they do not assist in any way with my problem -  - what you dont seem to understand is the 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

Therefore my  long lasting point is where in the vba code for the relevant txtdate  is the best place for the conversion and how do I ensure that the date is saved as per the user format - 03/10/2020 onto the worksheet so that the worksheet formula calculates the elapsed time and then - when the user calls on the data entry from the worksheet and which is mirrored in the userform list box - remains in the correct date format - 03/10/2020 and not 10/03/2020 

Does that make it clearer ?

Can you therefore just help to write the correct entry for the txt box or via the 'Sub_submit' script in the vba for the user form or as a sub routine in the module

i need to understand the way and where to convert 

Use CDate or DateValue before you write it into the cell.

https://docs.microsoft.com/en-ie/office/vba/language/concepts/getting-started/type-conversion-functions

https://docs.microsoft.com/en-ie/office/vba/language/reference/user-interface-help/datevalue-function

The cell must not be formatted as text, any other format is okay.

Andreas.

One more thing - please understand the original file used text in the date /time cells as a test on the sheet only - it is not the way it is operating now - so yes I am fully aware regarding the text issue - i need to understand the way and where to convert