Excel VBA Userform Date issue

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

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

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

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

Without a copy of your workbook I can't be absolutely sure but I think the code should be something like the following when assiging the time and dates to a TextBox.

'For Time test each of the following

Me.txttime.Value = Format(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 16), "hh:mm:ss")

Me.txttime.Value = Format(TimeValue(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 16)), "hh:mm:ss")

'For Date Test each of the following

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

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

It might even work if you reference the TextBoxes for time and date as follows without using .Value

Me.txttime = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 16)

Me.txtdate = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 17)

If you can't get it to work can you upload a copy of your workbook to OneDrive. If you have sensitive data then make a copy of the workbook and replace the sensitive data with dummy data. I sometimes use the following link to generate a random list of dummy names for test workbooks.

http://listofrandomnames.com/

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload under the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file name in OneDrive.
9. Select Share.
10. Click the link icon (Looks like chain links) at the bottom left of the dialog (Just above "Copy link").
11. Click Copy button.
12. Change back to this forum and click the "Insert Hyperlink" icon at top of the posting editor (Icon looks like chain links).
13. Right click in the Web address field and right click and paste (or just Ctrl V to paste).
14. Click "Insert" Button.