Excel VBA Userform Date issue

Hi Ozzie

Thank you so much - guess you spotted my frustration which i apologise for - Ill give that ago thank you and let you know - I did manage to get a work around by experimenting with Day/Month formats in the VBA and spreadsheet -I got it to work - the only issue being while cell format appears correct and works fine for the elapsed time formula - the date in the worksheet formula bar still shows mm/dd/yyyy but thats working ok at the minute

Thanks Again

Adrian

Andreas

One last thing from me - and Im so glad Ozzie has picked this back - thank goodness

I really do not understand why you continue to mither on about me not understanding that excel considers dates as numbers !!! /I have told you on every occasion that  I know that- to make your veiled insult that I dont understand this is rather rude actually

What you refused to do unfortunately was understand my problem - how many times did I ask you for help with coding the txtbox value in to a number/date format so that the worksheet worked correctly while using the regional date format - you are wrong by the way that it makes no difference - take my example of obtaining elapsed time

you say 12-5 is 7 yes that is true but when it 5-12 the answer is not 7 !! and that is the point - when data drops into excel as a date related number it depends on the format of the date - the serial number for 07/11/2020 (44142) is not the same as 11/07/2020 ( 44023) therefore when subtract 07/11/22 from 08/11/2020 the answer correctly is 1 day- if that date format in excel has been changed - for what the issue - and has compromised the normal dd/mm/yyyy setting to the CDate(USA) default for excel the calculation is now 11/07/2020 subtracted from 11/08/2020 !! - 31 days !! The change can be seen between the input on the user form - 07/11/2020 in the date box while in the relevant cell on the spreadsheet - 11/07/2020 - which mm/dd/yyyyy

This is what I always was saying to you!!!! Ozzie totally understood this and he even advised you about the issue with CDate and dd/mm/yyyy (UK)format - its nothing to do with Arabic formatting or anything else and if you have done a 'similar' project in the region - how come you took me for a dance around the garden? and by the way if you are doing work in this region please DO NOT use any of the material I shared with you - if id know you were working commercially on this forum I would not have shared this program with you

I asked you to tell me how to resolve that issue - and retain the visual appearance on the worksheet and user form as dd/mm/yyyy - purely to avoid any misunderstanding by the user - not me !!! simple as that

So in finishing thank you for your attempts to assist me - but in the future please take care to understand the root issue

Thank you

The calculations on the spreadsheet in all respects work fine - EXCEPT where the date format has been reformatted from dd/mm/yyyy to mm/dd/yyyyy which then distorts the elapsed time by over one month

Ossie, I have to apologize for interfering again, I will pass this thread on to you again, I just want to say one last thing:

I specifically quoted Adrian's part, which shows the whole dilemma. We both know that a date in Excel is a number, therefore the result of a calculation like =12-7 will always be 5, regardless how the cell is formatted. Maybe you can convince him that he has to write a number and not the date.

I think I did my best to explain how Excel works, unfortunately my didactic doesn't seem to be good enough to generate an understanding.

There is no problem with the date conversion, neither with VBA nor with Excel, it also works across languages. I can say for sure that it also works for the Arabic-speaking area, I wrote a similar program for a customer in this region in the past.

Good luck and stay healthy.

Andreas.

Hello again Adrian,

I opted out of this thread because I did not want you to become confused by different methods adopted by Andreas and myself. However, as it appears that you do not yet have a solution to your problem of writing dates from text in VBA to the worksheet I thought that I should opt back in and perform a test to establish if the method by Andreas of converting the text to dates is the problem or some other problem exists.

I have uploaded a workbook to the link below as a zipped file. Download and extract the workbook and test. Insert dates and times into the textboxes in the d/m/y format and see what result you get after clicking the "Calculate Time Difference" and it writes the dates and times back to the worksheet.

You will see that I have used the Split Function and the DateSerial function to convert the text dates to real dates. I am still dubious about the use of CDate but the method I have used performs a definite conversion based on the text date in d/m/y/ format.

Note the code to convert the time difference date to text for the time difference textbox. It is necessary to use the worksheet.text function because VBA does not work with retaining hours when the time difference is greater than 24 hours.

I have not attempted to use your method of assigning to the ListBox and then writing all to the worksheet because that is not required for this simple test.

If the test does not work then I believe there is a problem with VBA and dates when converting from text to date format and it could be applicable to different regions even though we both use d/m/y format. However, if it does work then I believe that CDate is not working as intended in your regional setup.

Link to the workbook: 

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

Hi Andreas

I really wish I could speak german

Please please follow slowly what im trying to say to you

1/ There is no special date format regional or otherwise needed - its simply UK - dd/mm/yyyy that is the regional setting/ cell format for my PC and excel - nothing complicated

2/ I do not want to do anything fancy - other than have the user fill in the relevant dates as per the userform questions - that being the date of the call and the date the incident finished only

(I dont want to touch anything regarding the 'now' date or time - that is irrelevant to this issue and it works and even gives the correct formatting!!)

The calculations on the spreadsheet in all respects work fine - EXCEPT where the date format has been reformatted from dd/mm/yyyy to mm/dd/yyyyy which then distorts the elapsed time by over one month 

3/ the date format needs to be dd/mm/yyyy - approximately the same as germany!! - currently, if - as you say you enter the date as dd/mm/yyyy into the USERFORM text boxes ( as text or otherwise  - ok I am aware of that !!!) excel on the worksheet - if the day is between 01 - 10 changes the date to MM/dd/yyyy where the day is < the 10th day of the month !! and that is the problem!!! 

4/ I dont understand why it is necessary to 'parsedate' anything, as the date format is a standard format ??? I do not want to run any un-nccessary functions for such a simple operation

5/ So again -PLEASE !! I need to know how to set the 

.Cells(irow, 6) = frmincident.txtdatefinished.Value to be formatted as a date (dd/mm/yyyy)

so that the text or 'Value' is passed to excel as a date - simple and as you keep saying!!. The list box entry should now show the same as the worksheet entry in the format dd/mm/yyyy WITHOUT swapping the day/month around - this has been the root problem

6/ Then when the data gets called back into the Userform by the user selecting an entry from the list box and pressing edit -

using

  Me.txtdatefinished.Value = Format(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 5), "dd/mm/yyyy") -

I actually think that this is working ok because what is with in the worksheet for the 'Value' comes into the List box in the same way and is the original guidance from OssieMac

the 'txtdate' and 'txtfinished' box should now show the value in the box as dd/mm/yyyy 

-Please now does that make sense ?

7/ I just need to know what code to write - that is the expressions/operators to add to the above lines to get subroutine to save the .values to the spreadsheet in whatever is required - nothing to do with wanting to use text or what excel does - Im happy that I understand what excel does when a text string appears as if it is a date 

8/ The issue is on the user form not the spread sheet !! Correct?

Please please lets finish this simply without talking around how excel looks at text and dates 

Regards

Adrian