Excel VBA Userform Date issue

hi ossie

it worked with this one

Me.txtdate.Value = Format(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 17), "dd/mm/yyyy")

it was still changing the d and m around 'usa' style so 09/08/2020 was 08/09/2020 but I changed the format to mm/dd/yyyy and it seems to work is that correct?

One last thing though please - in the worksheet there is a calculation in cell/column D which subtracts the incident arrival time from the completion time - thats fine except if the arrival time is 23:45 and the completion time is after midnight so it would be actually in the followiing days 24 hour period - im obvious getting an error/no time - any thoughts how I can do that in vba rather than on the sheet ?

I guess im going to need an additional txtbox on the user form to confirm the date of completion but then I guess Ill need to concatenate the first day of the incident and the time of arrival and then subtract the data and time from the first value - ive no idea what either the excel code or the vba would look like for that

Thank you Again

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

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.

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

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.