0

I have a textbox for date display which shows the date in this format: 17/04/2012 but I would like to format it to shows up like 'April 17, 2012'. I tried this code

UserForm1.txtDate = Format(Long Date)

which I am getting syntax error from compiler.Can you please let me know how I can do it? Thanks

Community
  • 1
  • 1
user1760110
  • 2,256
  • 5
  • 29
  • 37
  • If he's using an excel user form, the text box does not have a format property that I can see. `UserForm1.txtDate = Format(UserForm1.txtDate.Value, "Long Date")` – Zaider Nov 16 '12 at 16:54
  • Bad tagging. This is Excel, not Access. – Fionnuala Nov 16 '12 at 17:16
  • Just found another somewhat related good [post](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba) It's focus is more on datepicker, text boxes, formatting dates. I personally like the broad perspective of the topic. – bonCodigo Nov 17 '12 at 05:17

1 Answers1

2

there have been couple of posts on this. Culprit seems to be your system date format. Use of short, medium, long formats will change the result when the system settings change.

  • In US systems mainly it's done as, e.g. Format$(yourdate, “dd/mm/yyyy”)
  • Where as European systems, e.g. Format$(yourdate, “short date”)

If we look at explicitly defining your format, you are formulating your regional settings!

Good e.g. short date won’t always be mm/dd/yyyy but could be dd/mm/yyyy. That’s the key advantage over actually specifying the format. Simply you can change how you want your date to look like instead of depending on the system (with or without your knowledge).

When we first encountered this, we had some making the VBA apps and sending it over to another in some other place where they have own localized system date format. Your phone rings... ;)

  • Short/Long Date: Display a date according to your system’s long date format.
  • Medium Date: Display a date using the medium date format appropriate for the language version of the host application.

In your case you could use, 

UserForm1.txtDate.Value = Format$(Date,"mmmm dd, yyyy")

Or to be super-safe,

Dim dtDate as Date
    dtDate = DateSerial(Year(Date), Month(Date), Day(Date)) 
    UserForm1.txtDate.Value = Format$(dtDate, "mmmm dd, yyyy")

Hope that helps.

bonCodigo
  • 14,268
  • 1
  • 48
  • 91