Excel VBA Userform Date issue

Hi Ozzie 

thank you - better to check everything from scratch - and yes all is in order as per UK dd/mm/yyyy settings as default regional settings - as mentioned (im from uk!!)

So Ill try the file you sent across but Im also wondering to try some along the lines of setting the txtbox value as a variable like

Sub_txtdate_afterupdate()

Dim=Calldate As Date

On error resume next

Calldate = CDate (Me.txtdate.value)

Me.Calldate.Value = Format(Me.txtdate.Value, "dd/mm/yyyyy"

Ignore the syntax mistakes etc but do you think that may work? and the same for datefinished - have I missed anything?

Adrian

the date in the worksheet formula bar still shows mm/dd/yyyy but thats working ok at the minute

Hi Adrian,

This suggests one of two things to me. That your Regional date setting might actually be m/d/y format or there is a bug in Excel whereby Windows is not recognizing your Windows date settings. The date displayed in the formula bar (and also when entering/editing a date in the Formula Bar) should always be in the regional date setting irrespective of the NumberFormat used in the worksheet cells to display the date. In my case it is always in d/m/y format in the Formula Bar even if I have NumberFormat set to m/d/y on the worksheet.

I'd like to ask you to check your Windows date settings. Please don't take any offence to me asking this or setting this out in step by step guidelines because I have come across situations previously where people have believed they have done something correctly but in fact they have followed the wrong procedure.

I can only provide these guidelines in English so you might need to interpret.

1. In Windows 10, Select Windows icon (Bottom left of screen)
2. Select Settings (Gear cog like icon)
3. Select Time and Language
4. Maximize the window (This is because the position of what what is displayed on the screen is dependent on the size of the window and also the left column might not be visible and it becomes difficult to explain the following)
5. Select "Date, time & regional formatting" (Top right of screen when screen is maximized)
6. Under "Regional format data" heading does it look like the following screen shot with the Short date in d/m/y format (where I have enclosed in the red line)?

I am not suggesting that you change your regional date settings if it is normally m/d/y because if your application needs to be run by another person then they would also need to change their date settings and that is not practical or advisable. However, if we are certain of your date settings then the project can be handled accordingly and using the VBA Split function and DateSerial function to convert a Text Date does that.

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.