thank you reading my question once again. I am entering a date in a column range and I notice difference in format between the two dates entered using following method. Method 1: I have a custom made userform to enter the date in the active cellas seen in Date picker-custom date picker pop-up
Method 2: I use the Cntrl+: together to enter the date.
I although have the cell format same but I notice a difference in the two date which I investigated by using the LEN formula in excel.
LEN gives me for the date 05.08.2021 as 10 by method 1 and by method 2, the LEN is 5. In the next step, I use this date to derive the calender week number and since there is difference in both the methods, I get error using the medthod 1 and it works fine with method 2.Most probably it is becuase the method 1 hold a svalue which is very long and since in my calender week vba I have this dim as integer i get the error.
Thank you and hope I recieve help here.
Two same dates created by the above two methods explained in the second column where results are 5 and 10. It is derived by using excel formula LEN. My week number vba which extracts week number from these dates works only on the date which has result 5 i.e when the LENGTH of a string is 5. But when its 10 for example in this case I get run time error 6 as show in the picture Run-time error 6
When putting dates into cells you shouldn't use Format as that converts them to text.
Instead you should use CDate or DateValue to get a 'real' date value that you can format as you want, and use in calculations/comparisons.
So, in the posted code try replacing this,
Range(cCell).Value = Format(sDate, DatePickerX_DateFormat)
with this.
Range(cCell).Value = CDate(sDate)
Range(cCell).NumberFormat = "dd.mm.yyyy" ' added on 05.08.2021
Note, you might not need the 2nd line as Excel might automatically format the date as required, based on locale settings of course.