Formatting Dates with "-" separators and not "/"

0
votes

A file is given to me in such a way that the date is included in the file name.

I have written a code to retrieve the file based on the date.

The file name, which cannot be changed, is xxxx_MM-DD-YYYY.

For half of the month my code works. e.g of the file name is xxxx_05-15-2020, the user can enter in the date, 05-15-2020, and it will be retrieved.

If the user puts in 05-03-2020 , Excel changes this format to 05/03/2020, and based on the new format, the code will not work as it is looking for "-" separators and not "/".

How can I choose MM-DD-YYYY formatting?

1
excelvbadateformat
What is your local default date format? dd/mm/yyyy ? – Tim Williams
If you format the input cell as text then Excel will not do anything with whatever the user enters. – Tim Williams

1 Answers

1
votes

The following should work regardless whether your date is stored as a date type variable or string type variable:

dateString = Replace(dateVar, "/", "-")

If you need to convert the date to a date type variable, you could use cDate like this

cDate(dateString)

Assuming your date is stored as a date type variable, you could format it this way:

Format(dateVar, "dd-mm-yyyy")

Read more about the Format function here