Excel VBA Userform Date issue

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.

Thanks Guys - really good stuff and I appreciate your time - one question I guess remains - the elapsed time calculation originally took place on the worksheet therefore for each entry the relevent response cell contained the subtraction formula ie =IF(E16-G16=0," ",E16-G16)

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?

Thank you

Adrian

I can't find anywhere that I used n for minutes. Where in my code do you believe you found it? Have I overlooked a typo somewhere and can't find it?

Hi Ossie,

you must use n for minutes, m is always for month in VBA.Format:

The problem with the US / UK dates arises only if we write a text into the cell, and that is what the OP does.

I have a German PC and I have a dot as date separator, today in Germany is the 14.05.2020 which is the 05/14/2020 in US. But that is not the point.

If we have real dates in the sheet, Excel converts the dates to the local format, regardless where you are on the world. And also CDate uses the local format to convert a text into a real date.

The next step to support the local date format at the users end is to use VBA.FormatDateTime instead of VBA.Format to fill the Textboxes. After that it works where ever you are.

Copy the code below into your sample Userform, run it and click the button.

Here is a screenshot from my German PC:

Here is one from an US virtual machine:

As you see it doesn't matter which local format the end user has.

Andreas.

Private Sub CommandButton1_Click()

    Dim tmeTimeDifference As Date

    '(Subtract the Sum of Start Date and Time from the Sum of Finish Date and Time)

    tmeTimeDifference = Abs((CDate(Me.txtFinishDate) + CDate(Me.txtFinishTime)) _

      - (CDate(Me.txtStartDate) + CDate(Me.txtStartTime)))

    Me.txtTimeDifference = IIf(tmeTimeDifference >= 1, CLng(Int(tmeTimeDifference)) & " days ", "") & Format(tmeTimeDifference, "hh:nn:ss")

    'Example: Write the Date variable into the cell! Never a string!

    Range("B3") = tmeTimeDifference

    Range("B3").NumberFormat = "d ""days"" hh:mm:ss"

End Sub

Private Sub UserForm_Initialize()

  Dim F As Date, T As Date

  'Fill some random dates

  Range("A1") = Now + 5 * Rnd

  Range("A2") = Now + 5 * Rnd

  Range("A3").Formula = "=ABS(A2-A1)"

  Range("A3").NumberFormat = "d ""days"" hh:mm:ss"

  'Get the lower date into F, higher into T

  If Range("A1") < Range("A2") Then

    F = Range("A1")

    T = Range("A2")

  Else

    F = Range("A2")

    T = Range("A1")

  End If

  'Fill the Textboxes

  Me.txtStartDate = FormatDateTime(F, vbShortDate)

  Me.txtStartTime = FormatDateTime(F, vbLongTime)

  Me.txtFinishDate = FormatDateTime(T, vbShortDate)

  Me.txtFinishTime = FormatDateTime(T, vbLongTime)

End Sub