Excel Userform: UK (dd/mm/yyyy) format not working when written to cell

  • Hi There, thank you for checking out my problem.

    Object of the form:
    When date is entered (dd/mm/yyyy) in the date textboxes, displaying dd/mm/yyyy in the textbox, it retains the format when it is submitted to the spreadsheet in the next empty row.
    I will also be creating a ‘find record’ (in a multipage form), where when a user enters the search criteria, it populates the date textboxes in the correct dd/mm/yyyy format.

    The issue is that when a date is entered into the textboxes Txt_StartDate and Txt_EndDate as dd/mm/yyyy, that when they are submitted to the worksheet, they revert to the mm/dd/yyyy or the date as a text format.

    ‘Normal’ dates 1st day to the 12th day of the month goes to mm/dd/yyyy format in the sheet, but dates from the 13th day to the 31st (or last day of the month), are stored in the sheet in TEXT format.

    Current Coding based in the Calculate button:

    I have tried the following settings on the worksheet cell/column formatting;

    • No formatting at all (which should defaultly store the date as a date as per the system setting (dd/mm/yyyy) – stores as Text or American (US) date format
    • Formatting cells/columns to dd/mm/yyyy still switches the date to the US date format

    VBA coding attempts:
    I have tried formatting the code of the textbox date

    • Using the cdate format,
    • Format(Txt_StartDate.Text, "dd/mm/yyyy")
    • Format$(date1,"dd/mm/yyyyy")

    None of the attempts work.

    Currently the code is in the ‘calculate’ button, but I have tried it in the Sheet Sub and Module Sub and neither of those worked either.
    Now I am more frustrated & confused.

    • My system time zone is (UTC+10:00) Canberra, Melbourne, Sydney, in the date format of dd/mm/yyyy.
    • Excel 2016

    I have been struggling over 18 months trying to find a solution for successfully converting/formatting dates entered in my Userforms into the UK (dd/mm/yyyy) date format into the worksheet.

    I have tried and tested in all likeliness ALL the YouTube tutorials and various forums that promote that they have the solution, but none have fixed the problem on my system (or my work computers).

    Could I ask a small favour, that when you test the proposed solution, that you change your system time zone to one that uses the dd/mm/yyyy date format and test dates under and past the 12th day of a month, just to confirm that it does what it is intended to do.
    If you could advise me if there is a way that I can code the date format as a global default, that would be fantastic. Just let me know where the code has to be written (eg, userform, module, worksheet, workbook).

    I've attached the sample file for your reference.

    Thanking you in advance,
    Kind regards

  • Unfortunately, this seems to be a common issue with a number of VBA date related functions for those of us down-under (I'm amazed how often VBA seemingly ignores local date format).

    Have you tried a) using a date format of dd/mm/yyyy in the target cell for the date and b) something like

    Cells(endRow, 1) = DateValue(Format(TextBox1.Value, "dd/mm/yyyy"))

    with whatever is your Textbox name.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!