0

Good day

I am trying to build an add-in that can handle values from and into a MySQL database the same way regardless of the Windows Region settings.

When the region settings has set the Short Date as "dd MMM yyyy" I have no idea how to change that format into "dd/mm/yyyy"

When I use:

Format(Date, "dd/mm/yyyy")

It translates to: 05 06 2020

Which errors out on the database. I have tried CDate and DateSerial, all to no avail.

I essentially set my Global Const variable called dateFormat as:

Public Const dateFormat = "dd/mm/yyyy"

And then I use the following to try and write to values into the Textboxes I use:

txtCreated.Value = Format(Date, dateFormat)

But the Textbox then contains the value as: 05 06 2020

When writing this value into my db, I use:

rs!ncreate = Format(txtCreated.Value, dateFormat)

And then get a date formatting error saying the format "dd mm yyyy" is not accepted even though I am trying to send it in format "dd/mm/yyy"

Any ideas?

Improve this question
6
  • For reference, here is the doc for the format function... Per that doc, the / is the date separator but: The actual character used as the date separator in formatted output is determined by your system settings. Commented Jun 5, 2020 at 12:24
  • My reigon settings have "dd/mm/yyyy" as default short date setting so it's a bit tricky to test - you can perhaps use the backslash \ per the previously mentioned documentation to display the / as a literal character? Commented Jun 5, 2020 at 12:33
  • 1
    Thanks Samuel. Your second comment only returns "05m6y20157". The user's don't want to change their Region Settings. I want my add-in to work with potentially any region date format if that makes sense. Commented Jun 5, 2020 at 12:38
  • Yes I understand the requirements - and I feared the 2nd suggestion might return something like that (it's correct for me but as I suspected it's due to my region) - I'm having a look around now, I'm sure I've found some useful info about dates and regions before that should help... Commented Jun 5, 2020 at 12:40
  • 1
    Thanks. I've used that now to conjure up my own Function. Was leaning towards that solution. I'll post mine as an answer below. I really appreciate your help, thanks. Commented Jun 5, 2020 at 12:47

2 Answers 2

Reset to default
2

To use this Microsoft doc as a reference, this would output your date as desired:

Public Function MakeEuroDate(DateToFormat As Variant) As String 
 
    ' Do nothing if the value is not a date. 
    If Not IsDate(DateToFormat) Then Exit Function 

    ' Format the date value as "dd/mm/yyyy" date formatted string expression.
    MakeEuroDate = "#" & Day(DateToFormat) & "/" & Month(DateToFormat) & "/" & Year(DateToFormat) & "#"

End Function

Note: leading zeros are removed in this output so the first day of a month would be 1 not 01.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Excellent. Thanks. I'd just make the last line: MakeEuroDate = Format(DateToFormat, "dd") & "/" & Format(DateToFormat, "mm") & "/" & Format(DateToFormat, "yyyy") Otherwise the day and month isn't 05 and 06, but 5 and 6.
Another option is to keep the original format dd/mm/yyyy and use Replace to change the spaces to /. Nothing will happen if the formatted date already contains the /.
@RenierWessels You should add your answer back in (that does what your comment states) - It's OK to answer your own question if it better provides a solution! Also, per BigBen's comment, using the Replace function on your original output is also another way to tackle this with far less code!
Done. Thanks. I'm just weary that the replace might not always return the expected output. I think the function will.
Because Format doesn't always work. I've had Format(Date,"dd/mm/yyyy") return "5 6 2020", "6 5 2020", "05 06 2020", "05 Jun 2020" and "06 05 2020" from different region settings. Merely replacing the spaces with / wouldn't work for ALL use cases. The function method is safer because the day, month and year's format, the separator as well as the order in which they end up in can be controlled.
1

Based on Samuel's suggestions in the comments on my post, I have built a custom function to convert dates for me now:

Function FormatDate(DateIn As Variant) As String

    ' Do nothing if the value is not a date.
    If Not IsDate(DateIn) Then Exit Function

    ' Format the date value as "dd/mm/yyyy"
    FormatDate = Format(DateIn, "dd") & "/" & Format(DateIn, "mm") & "/" & Format(DateIn, "yyyy")

End Function

Using the Format() function allows the output to show leading zeros, for example; the input "1 May 2020" would return #01/05/2020# rather than #1/5/2020#.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.