Excel VBA Userform Date issue

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.

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.

Andreas,

I did misunderstand your comment. I thought you were indicating that I had use n in lieu of m.

I question your comment: youmustuse n for minutes, m is always for month in VBA.Format:

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.

In relation to converting to dates and whether to use CDate or the method I used, the OP had already indicated he was having a problem with it converting to US style dates instead of British. I don't know what the OP's regional date setting is and thought that it might be the US format but for his project he was using British format (I have previously seen that occur) and that was the reason for the method I used.

Because you have bought into this I will bow out and leave it with you to answer. I am sure that the OP can do without multiple people answering the questions.

hi Andreas

I think you may have understood - the userform only records data inputted from an operator - this is then copied into the database which sits on a worksheet

There is no point doing any calculation on the user form

Therefore once the data is in the data base the worksheet is conditionally formatted to highlight achievement or other wise of Strategic KPIs - for example if a call handling time is 2 minutes then the KPI has not been achieved and the cell is highlighted red - if the call handling time is 1 minute the cell and calcluated time difference is green etc - do you get the point ? The database is then interrogated on a weekly monthly basis by a separate analysis team - not the data input person.

Regarding the 0 suppression I had tried that and it didnt work for the Date/Time functions - particularly reading back into the VBA Userform List section

I dont if that makes sense now - any help appreciated

thank you

Adrian

Can I use a vba method to reflect the formula down the column as each new entry from the user form is saved rather initialise the calculation via a command button on the user form?

I don't understand the point of your question.

You've created a Userfrom to calculate a date/time difference... what should be the sense of all that if you calculate the difference with a formula in the sheet?

BTW, do you know that you can suppress 0 values in a sheet?

File\Options\Display options for this Sheet\Show a zero in cells that have a zero value

If you uncheck that you just need =E16-G16

Andreas.