Excel VBA Userform Date issue

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

Hi Andreas,

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?

Reason for not using Cdate with dates extracted from TextBpoxes is the problems different people have with US style dates (m/d/y) and the British Dates (d/m/y). They can return an incorrect date depending on the Regional setup of their compouter which is one of the problems the OP is having. By using the Split and then DateSerial, the developer can handle the output based on the format of the date they are using in the TextBox.

I understood that the time difference as described by the OP only required the time because the jobs could go past midnight but not past the next midnight but maybe I have misunderstood. However, if days and time is required then it can be handled by changing the output display.

https://1drv.ms/u/s!ArAXPS2RpafCsxEa9AyUHcRu6EaF?e=zolnSg

A few constructive thoughts about it:

The minute formatter for VBA.Format in "n" not "m".

https://docs.microsoft.com/en-ie/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications#time-symbols

Why struggle with separating strings and recreating dates? Isn't it easier to use VBA.CDate?

And if we have different days, shouldn't the output on the form contain the number of days?

And of course, a Userform for real live needs a lot of code to catch invalid input by the user. But this is another story.

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 = (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("B1") = tmeTimeDifference

End Sub

I edited the file that I uploaded and re uploaded so ensure you go to the post to select the link to download. This message because you don't get notifications of edits on the post and if you try to download from the link in the email notification then it probably will not work.