convert string to date format of "d-mmm-yyyy"

anyone got any ideas?

Hi

Have you had a look at the region setting for the PC? Your profile doesn't say where you are located.

rylo

i am not sure i understand........i am located in the US........

Hi

Go to the Control Panel, Regional and language options, regional settings, and not your current selection. Then change it to English(Australian) and try you code again. If it works, the I'd say it has something to do with how CDate is converting your string to a date.

Make sure you return your regional settings again.

rylo

ok, here is the thing.

I am writing some code for a coworker who is another country.

I ask the user to input a date in th format "dd-mm-yyyy" ......i use an input box for this.

i try to convert this date string into a date ........and into a specific date format.

i am pulling out my hair.........any suggestions.............

ok, so i have been thinking about this a little and i think this is my problem........but i will leave it to you guys......

bu here is whati am thinking.

I am asking the user via an input box to input a date in a specified format of "d-mm-yyyy". So if the user puts in 9/12/2011 this is supposed to be December 9th 2011 but my excel interprets this date as Sept,12 2011........

i need to ask the user to input a date............thing is i need to know what "format" they input the date in.

Hi Welchs

Your code DIMs sets test_date as a Date. So, it is a value, not a string. You try to set it as a string using the Format command, but it hangs on to the date value only (not the text format).

So, either Dim test_date as a string, or move the Format command to the MsgBox command:-

Please Login or Register  to view this content.

Regards, Rob.

(this thing is only letting me do a quick reply .....so )

This is what i came up with........see code below(I could not put it into the format i know i am supposed to because this thing is only letting me do a quick reply).

Question:
What i have works but ONLY IF the format i put it in is "dd-mm-yyyy"..........if i try to use "d-mmm-yyyy" my code does not work.

Any ideas?

I am trying to take input from the user "specifying" the date format they are supposed to input the data in.......i then take this date/data and subtract off another date and compare the delta




Dim fl_name As String

fl_name = ThisWorkbook.Name
Dim test_date1_string As String
Dim test_date1_date As Date
Dim test_date3_date As Date
Dim user_input_date As Variant
Dim junk1 As Date



Set ws = Workbooks(fl_name).Worksheets("Sheet1")

Dim StrtDate As String
StrtDate = Application.InputBox("inptu date as dd-mm-yyy")
If StrtDate = "" Then
date_error = True
ElseIf StrtDate = "False" Then
date_error = True
ElseIf StrtDate <> "" Then
'user_input_date = CDate(StrtDate)
StrtDate = Format((StrtDate), "dd-mm-yyyy")
user_input_date = DateValue(StrtDate)
'user_input_date = Format(CDate(StrtDate), Std_date_format)
date_error = False
End If



MsgBox (user_input_date)

junk1 = DateValue("12 / 8 / 2011")

MsgBox (user_input_date - junk1)

am i doing this the best way............to be honest i came along this soln by accident.......i was just putting values and it happen to work......but i am not sure why.........

since i have a lot of questions about why this code is working.............i think i probably should not use it.

Instead i am thinking of asking 3 questions:
1) input year
2) input month
3) input day of month..........

i think i can then use these three to get the right date no matter what...........