Excel VBA Userform Date issue

My interpretation of the info in the link you provided is that m can be used when preceded with h so when used in combination with hours it is valid. It all works for me that way.

Your interpretation is correct and yes, it works on most systems.

I've stumbled upon this problem myself in the past, so I can say that "m" doesn't always work on all systems, but "n" always works.

I should have said "you should" than "you must", sorry, my bad.

Andreas.

do you get the point ?

Hi Adrian,

not really, but that doesn't matter. The point / problem with your database and code is that you have Text instead of real date/time in the cells.

I know you think it doesn't matter, because the formula in D,F,H,J works... but why is that is a different story.

Trust me that is an error and it must be corrected:

Columns E,G,I,K is Text and you have errors:

On a German system you can see that all dates with a dot as separator are valid, all others are text and wrong and invalid too:

If you have text in the cells, your file will never work. That is the point.

Change all date/times to real date/times in the sheet.

Use Date variables as Ossie suggested

Use code with CDate and FormatDateTime and write the Date variables into the sheet and your problem is gone.

Andreas.

Hi Andreas and Ossie

Thank you both for assistance

Andreas - yes I know text was incorrect but that was the point of the original help request because text actully does work up to an extend until you have a date leading with 0 - I posted to the request to seek assistance with that issue which Ossie resolved and correctly in respect of the UK format

So stepping back - regarding the the post in respect of using passive vba (rather than a command button on the user form) or a formula in the workbook - is there away to carry out that calculation while the user is inputting the data for each each a new incident record is generated?

Lastly can I create a list on a user form which adds items each a new item is added from a combobox list without creating a complete new record every time a new item is added- thinking of a way to explain - it would be like shopping online where your online shopping basket acts like a list box and then copies all those items when your finished onto the worksheet when you press SAVE

is there away to carry out that calculation while the user is inputting the data for each each a new incident record is generated?

You can use the Change event of a Textbox to execute a code while the user is typing.

Choose the related Textbox on the left and the Change event on the right drop down.

Andreas.

Hi Andreas

I think im well aware of how to use a change event thank you 

My question is regarding the code i need on the user form - also the other point regarding the list box please