Excel VBA Userform Date issue

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.

It appears that you have a handle on most of the code so rather than download your workbook, I have uploaded an example workbook with an example of how to handle dates and times, particularly for calculating the period that runs past midnight.

It is preferable  to have both the date and time when calculating time differences that run past midnight.

The code example handles the dates much better than my previous code examples by splitting the dates at the slashes and as the programmer, you are determining which are the days and which are the months in the TextBox values.

The Split Function splits the date value in the text box into the 3 values that are separated by the slashes and assigns them to a zero based array so you can then use each of the 3 elements with the DateSerial function and assign the date to a date variable. (Google DateSerial Function for more information if you are not familiar with it.)

As per the comments in the code, in VBA the Time variables are dimensioned as date variables. Times are actually only a fraction of a day. Therefore you can Sum the Finish Date and Finish Time and then subtract the Sum of the Start Date and Start Time to calculate the difference (or interval).

I have not included code to verify valid entries in the Text Boxes but you probably should test each Text Box for a valid entry prior to performing the calculations or you will get an error in the code, especially with blank text boxes.

Feel free to get back to me again if any further problems with it.

Link to the Example workbook on OneDrive. It is a zipped file so download and extract.

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

incident database - dummy data

As per the last - see attached now thanks

Hi Ossie

Thank you for that fast response !!

Ill give your suggestions a go and I'll forward the file - theres nothing sensitive in there - its all dummy test data Ive been adding randomly

However the text is in arabic !!! in some areas so you need to select times roman (arabic) from the option in vba tools menu first before opening the file - the form is intuitive so doesnt matter what you select from the drop downs but please use the tab key to move through the sheet

the last entry in the worksheet is now playing up - i entered 12/09/2020 and the form on saving changed it to 09/12/2020

cheers

Adrian