Excel VBA Userform Date issue

So now all data on the worksheet is saved in UK format dd/mm/yyyy as you pointed out - so now there are no text formatting of dates or times in the worksheet

The problem is that it seems that user form txtdate value, on saving to the worksheet is being saved in mm/dd/yyyyy format -

Adrian,

the only problem is that you do not understand that you can not save a date in a special format.

A date in Excel is a number, not a text!

Make a new file

Apply the formula  =TODAY()  into A1

Format A1 as General

And you'll see this number for 20 May 2020:

You have to write that number into the sheet, not a text, in whatever format!

(Technical details: If you write a text into a cell that looks like a date, Excel tries to convert the text into a date / into a number. That's the reason why you think Excel stores the date into a different format, but that's a wrong conclusion.

If the conversation fails, Excel stores a text into the cell. And it doesn't matter which format the cell has before or after. That's the reason for all that wrong dates / errors in your database.

Means: Your form will never work if you continue to write text.)

Again:

Convert the text from your Textbox into a date variable and write that variable into the sheet.

After that you can format the cell with any date format you like, doesn't matter.

To get the date from the sheet:

Read the value from the cell and use FormatDateTime to convert it into a text

I already showed you an example how that works and how it looks like on systems with different languages.

That works all around the world, even if you share the file.

Andreas.

Hello Andreas

Thank you again but again you are missing my point - I know that the value in the text box is text - what im askinis where and how do i make the date conversion ?

In my mind there are two options

1/ either a script that runs as the data (text) is entered into the text box ie the txtdate box by a code attached to an operation to the textbox eg before/after update or

2/ when the user presses the save button ( Sub_Submit) and the user form data is passed over to the worksheet and into the relevant cells

So that is question - the worksheet formatting is correct for the appropriate date format - so where is the most appropriate place to carry out the conversion ??

I have tried a number of different ways including adding FORMAT to the entry which swaps the data to the worksheet - taken from the above extrat i sent you

   .Cells(irow, 18) = frmincident.txtdate.Value = Format( ..........

This didnt work

Can you advise me on this as it is sending around the bend

Adrian

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

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.

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