+ Reply to Thread
Results 1 to 19 of 19

UK and USA Date Format Issues - Userform

  1. #1
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    UK and USA Date Format Issues - Userform

    This seems to be a really odd problem I'm having with userforms and dates. Specifically the American date format (mm/dd/yy) being used even though the pc and excel all runs on the UK date format (dd/mm/yy).

    So here is my problem:

    I have a button on a userform that generates a new ID number and the current date for new quotes.
    At first when I used it, even though the date on my pc is 11/06/15 (dd/mm/yy), it was generating the date 06/11/15 (mm/dd/yy).
    I found a fix to that by changing the date generation to:
    Please Login or Register  to view this content.
    This changed the date format back to 11/06/15 and when I saved the entry it wrote it to my spreadsheet as 11/06/15 (correct).

    At this point everything seemed to be running smoothly.

    However, I also have a button for finding quotes and repopulating all the data so you can go back at a later date and fill in the blanks.
    When it finds the date from the quote on the spreadsheet and fills in the text box on the userform, it goes back to the american style 06/11/15.
    When I press save this time it saves it to my spreadsheet as the american format.

    Please Login or Register  to view this content.

    Does anyone have a fix for this? I need it to generate a UK format date from today's date when I press a certain button and also keep the correct format of date when pulling the date from the spreadsheet.
    Attached Files Attached Files
    Last edited by Mousiefuzz; 06-12-2015 at 05:33 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,296

    Re: UK and USA Date Format Issues - Userform

    VBA is US-centric, whereas your worksheet is UK. When you save a date to the worksheet, make sure that your code is clear on how to convert the text to a valid date, along the lines of:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: UK and USA Date Format Issues - Userform

    Took me a while to figure it out, still getting to grips with how to thread code together in Vba.
    But eventually got this to work for both parts that I needed it to work for.

    I'm still having a problem when it comes to updating though as I have two methods of saving data to my worksheet.
    The first and the one that you have very helpfully fixed is my standard save. I input fresh information and the button writes it to the worksheet.

    The second is a Find and Update combination which I can't figure out how to work that code into.
    My Find button uses the Unique ID to grab all existing data up for that quote and the Update writes over the old information.
    The Find button is putting the correct date on the userform textbox. The update is writing the USA format still.

    How can I put this bit of code into my update to get it to work?
    (It is txtStartdat1 and txtStartdat2 that I need this to work for)

    Please Login or Register  to view this content.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: UK and USA Date Format Issues - Userform

    Assuming the dates are in the right format in the textboxes, just use CDate(txtStartdat1.Text) when writing to a cell.
    Everyone who confuses correlation and causation ends up dead.

  5. #5
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: UK and USA Date Format Issues - Userform

    Unfortunately they aren't in the right format in the textboxes or at least this is where I'm having the issue in figuring out where it is converting the date format.
    They are in the right 'order' in the text boxes, so when I type them out I type in 01/02/03 (1st Feb, 2003) but the vba is reading it as 01/02/03 (2nd Jan, 2003).

    The other bit of code helped when saving so that it read it the correct way around (dd/mm/yy) but I don't know how to put that bit of code into my 'Update' button which writes over old information instead of saving to a blank row, my issue is no longer what code to use (I don't think so anyway) but just my ineptitude in understanding how it reads the code and how the code works and fits into my existing code.

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: UK and USA Date Format Issues - Userform

    If your date settings are typical UK regional, CDate("01/02/03") will return a true date value of 1 Feb 2003, so you just need:
    Please Login or Register  to view this content.
    for example.

  7. #7
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: UK and USA Date Format Issues - Userform

    When I use that it is still returning the US date format.

    My textbox contains "01/02/03" yet it is still inserting "02/01/03" in the worksheet.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: UK and USA Date Format Issues - Userform

    How is the cell the date is going in formatted?
    If posting code please use code tags, see here.

  9. #9
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: UK and USA Date Format Issues - Userform

    Date - dd/mm/yy UK format

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: UK and USA Date Format Issues - Userform

    Which textbox(s)/date(s) are you having trouble with?

  11. #11
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: UK and USA Date Format Issues - Userform

    What exact code did you use? CDate always uses your regional settings to interpret date strings.

  12. #12
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: UK and USA Date Format Issues - Userform

    Two pretty much identical text boxes, "Start Date *" and "Start Date"
    http://i.imgur.com/946Fspr.png
    The date is typed into those two text boxes which are named 'txtStartdat1' and 'txtStartdat2' respectively.

    I type in the date "01/02/03" for example and when I press save, it saves that date as "01/02/03" in the correct cell on the worksheet.

    However, when I go back into my userform to update incorrect information, I use my 'Update' button instead of the save button.
    The difference between the two is one saves to a new line, the other saves over old information.
    It is the 'Update' button that is saving the date wrong. The information is coming from the same text boxes but I don't know how to fit the code into my update button to make it format the date correctly.

    This is the code I am currently using for my 'Update' button, specifically "txtStartdat1" and "txtStartdat2" are the ones that need formatting.

    Please Login or Register  to view this content.

    As for the code I used, I tried entering:
    Please Login or Register  to view this content.
    That into my 'Update' button. It didn't change the date format.

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: UK and USA Date Format Issues - Userform

    Are you using this for the two dates?
    Please Login or Register  to view this content.
    PS Why are you using variables for the values from the textboxes? Can't you just write directly from the textboxes to the cells?
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: UK and USA Date Format Issues - Userform

    I think this is my current problem. I have no idea why I'm using variables, it was a piece of code I had some help with that I've used and that has worked... Up until now.

    Should I in theory be able to rewrite all of that code instead as:

    Please Login or Register  to view this content.
    And if I did change it to that, how would I then insert that code to format the dates?

  15. #15
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: UK and USA Date Format Issues - Userform

    Please Login or Register  to view this content.
    and the same for the other date textbox.

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: UK and USA Date Format Issues - Userform

    You would use the 2 lines of code I posted in the second section of code.
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: UK and USA Date Format Issues - Userform

    Okay, that's all working much better, date in correct format and it's also a lot tidier.

    The only issue I'm having now is a run time error, type mismatch.
    It only occurs when there is no second date (txtStartdat2 is blank) which will be a lot of the time. What can I add in to stop this?

  18. #18
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: UK and USA Date Format Issues - Userform

    Perhaps.
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: UK and USA Date Format Issues - Userform

    You two have been invaluable help. Can't thank you both enough!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform Date Format Issues
    By gtbear in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-30-2014, 08:38 AM
  2. Date and Calendar issues in Userform
    By passman86 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2013, 07:18 AM
  3. Date format issues
    By robertrocks in forum Excel General
    Replies: 2
    Last Post: 11-05-2010, 01:32 PM
  4. Date format issues
    By sketchgal in forum Excel General
    Replies: 2
    Last Post: 07-20-2010, 07:33 AM
  5. date format issues
    By freekrill in forum Excel General
    Replies: 2
    Last Post: 02-16-2005, 07:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1