Date Format Change in other systems and due to that Macro doesn't work properly

[brajpalshishodia]

Hello Guys,

I am having quite a hard time to solve this issue as it's working seamlessly on my system and any other system which has system date format in US, i.e. mm-dd-yyyy, however, the issue is that client is from Europe and their system date formats are different and can be anything, e.g. dd-mm-yyyy or anything.

I tried everything to keep the date format constant but it's futul and generating the issue on client's system because the process doesn't run.

Here in the attached workbook "Main" sheet, there is a button called "Generate Billing/Contract" when it's clicked, an userform pop-up with 2 option buttons, 1st is for Billing Invoice & 2nd for Rent Agreement, after selecting whichever, there is userform which requires to be filled with the information from the "Reservation_Raw" sheet and create the Invoice/Agreement accordingly but issue as I explained, arise when system date format is other than USA standard date format because the data is also in US date format and in client's system, after selecting "Check In" date, they get "Check Out" date blank and other fields are also blank because all the fields are interconnected.

Below is the sample of code where issue arise:

Please Login or Register  to view this content.

I tried to attach the workbook also for your kind perusal but I am not able to as attachment option is not working while posting this issue here but if you need, please let me know how can I provide the workbook.

Kindly help me out in this as being stuck for last some weeks and due to this project is not finished.

Thanks a lot.

[Keebellah]

Here's how to attach a file

[brajpalshishodia]

Hello Keebellah,

Thanks a lot for sharing the SOP for attaching the file, it was quite helpful and clear.

I followed the instructions and was able to attach the file.

Here you will see "Generate Billing/Contact" button, when it's clicked, userform opens with 2 option buttons for Billing Invoice/Rent Agreement, select any one.

This issue is with Check IN and Check Out date fields.

It works fine with US date format, i.e. m/dd/yyyy h;mm:ss am/pm@ but when system local regional settings are different from US format then it creates issue.

You will see I have used FMT function in Module "Macro 1" and using standard format but it is also not working.

Kindly help, it will be highly appreciated.

Best Regards.

[Keebellah]

Got the file will see what I can do for you

[brajpalshishodia]

That's great, will wait, Thanks a lot

[Keebellah]

Some links that you should look at:
https://superuser.com/questions/7303...erns-in-formul
https://powerspreadsheets.com/vba-format-date/
https://bettersolutions.com/vba/macr...n-language.htm
These might give you some ideas.
I'll check what I have as code in my acrhives

[brajpalshishodia]

Yes Keebellah, Thanks for sharing the links, all these were part of my research in the process to rectify this date format issue and used "FMT" function in my code referring the 1st link, however, user has to change their system date format at least once to make it working, it won't work when user will open the file. He will have to go to Control Panel and then just some modification in Date and Time field, isn't necessary to make it US Format and when come back after clicking on Apply button, it start working.

Strange but this is happening.

[Keebellah]

One thing that you must avoid in all cases is that users need to make changes to their settings for your app to work.
This YOU have to solve inside the VBA, dates may be shown in any format as long as you can still person the necessary calculations. In one case the AM/PM will be visible and others not.
You have to read the user's system settings but not make them change it for your application to work.

[Arkadi]

Personally whenever possible I use dd-mmm-yyyy (the important part being mmm) as my date format in sheets. Otherwise I often run into problems where the month/day are mis-interpreted. By using the letters for the month, excel can't get confused as to which number is the month vs. the day.

When I changed the format of your date/time columns (B:D) in the Reservation sheet to "dd-mmm-yyyy h:mm" it seemed to work with different system date formats.

[brajpalshishodia]

Hello Keebellah,

Yes Sir, that's the same thing I also want to achieve that user doesn't have to refer his system settings at all and it should work for any type of date format but not able to do the same and looking for the help here.

[brajpalshishodia]

Hello Arkadi,

Did you changed the date format in Reservation tab or Reservation_Raw tab as for creating Billing Invoice & Rent Agreement, Reservation_Raw tab is used. One more thing I want to know that how were you able to verify that it's working for different system date format. You verified it on any different system or on your system by changing the date format through Control Panel.

Many Thanks for your reply though.

[Arkadi]

I guess in my test I forgot to close/re-open excel when I changed date formats. Will keep looking at a solution, but what I tried before did not work.

[brajpalshishodia]

Okay...appreciate your concern to find the solution, that will be great relief.
Best Regards.

[Keebellah]

I would like to point out that the date format is only a visual interpretation,
did/mmm/yyyy makes what you see clear and will display the date January 3, 2019 either as 03/jan/2019 but the system date might interpret it as March 1, 2019 and show the result in the cell as 01/mar/2019
The only way to avoid confusion is to always use the serial date value so you know for sure what date is being used
I struggled with this for years but solved it by always making sure that the date was a serial value (also with the time value) and the format the cell or text box accordingly for the visual part
Hope i’ve been able to explain myself

[Greg M]

Hi All,

Just a general comment regarding dates:

Dates and Times are STORED as numbers on a worksheet, but are DISPLAYED in whichever format the User has selected.

So far, so good, BUT once a numeric date value is sent to a TextBox, ComboBox, Dropdown List etc. the value becomes a STRING and must be processed appropriately so that it is "sent back to Excel" as a numeric value for entry into a worksheet cell, calculation, use in a filter etc.

Regards,

Greg M

[Arkadi]

@gregM... Exactly! And the op is using tucked away ranges, textboxes, comboboxes... Hard to follow it all and find the best solution. In addition the format function (FMT) he is using formats differently than the cells. Tbh I'm surprised it works even in one regional format

[Keebellah]

True, and that's why I said that you should always make sure whatever you process make sure you bring it back to the correct numeric (serial) value, requires extra code but that's the only way to get it working.
A textbox on a userform is okay to show what the user has selected but the calendar should at all time be used to select a date and a date variable to hold the selected date, not just the textbox.
I'll extract my data form from a file I have and attach it later, explains it better, tells more than all the words

[Keebellah]

I didn't find the correct file but I did find my vba module that solves some issues.
Some reference is in the code as to it's origin but som things were edited by me.
Place this module in the VBA Project, and then use the following syntax in the vba to format the date (no time just date)

Please Login or Register  to view this content.

FormatDate is the string result depending on the system's settings.

Take a look and see if you can implement it, you can always edit the vba module to include the time part

[brajpalshishodia]

Hello Keebellah,

Thanks a ton for sharing DateModuleInternational.Zip and the suggestion to use date as serial number not as text in the code, it was really helpful and finally solved the issue with which I was struggling a lot.

Highly Highly Highly appreciate for your patience with this issue and provide the solution.

I would like thank Arkadi, Greg M for their valuable inputs as well.

All in all it has been a great learning for future.

Best Regards,
Brajpal Shishodia

[Keebellah]

You're very welcome, that's what we all hope to achieve, pass on ideas we ourselves found and reused or modified.
Always learning, never too old to learn

[brajpalshishodia]

Yes, can't agree more, therefore, sharing the working template here if anyone need in future to get the solution.

Many Thanks again.

Best Regards.