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:
Option Explicit Private Sub Cmb_Calculate_Click() Dim date1 As Date, date2 As Date Dim endRow As Long 'Dim ws As Worksheet date1 = Format(Txt_StartDate.Text, "dd/mm/yyyy") date2 = Format(Txt_EndDate.Text, "dd/mm/yyyy") If date1 > date2 Then Txt_StartDate.SetFocus MsgBox "The start date cannot be greater than end date" End If Txt_DateDiff.Text = DateDiff("d", date1, date2) endRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Cells(endRow, 1) = date1 Cells(endRow, 1) = Format(Cells(endRow, 1), "dd/mm/yyyy") Cells(endRow, 4) = date2 Cells(endRow, 4) = Format(Cells(endRow, 4), "dd/mm/yyyy") Cells(endRow, 3) = Txt_DateDiff.Value End Sub
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")
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,