Excel VBA - Convert Date format 1

[CDebet]

Hi,

System date format is "DD/MM-YYYY".

I need to output a date format like "YYYY-MM-DD".

Without changing system date format, but within the macro, how can I convert the present "DD/MM/YYYY" to "YYYY-MM-DD"

In advance thanks!

CDebet

 

[xlbo]

Format(timevariable,"YYYY-MM-DD")

or

just have the cells formatted correctly - that way it doesn;t really matter how the date gets into the cells...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

FYI, faq68-5827.

Select the column of dates

Data > Text to columns - FIXED - [NEXT][NEXT] - Select the DMY Column data format and finish.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CDebet]

The date is entered by the user through a text field in a form, and is in the end used in an xml-file. The logic for the application that in turn will read this file, expects a "YYYY-MM-DD"-format. The way I see it, conversion can be done in two ways: 1) Assign the date-field value in the form to a proper formatted cell in Excel, and then fetch when generating the export file or 2) Format the date in a variable in the code.

I have tried both. 1) The value from the form, stays in the same format as in VB when stored in an Excel-cell. If I activate the cell, and press Enter, it gets the correct format. As for 2), I have tried the "format"-code mentioned above, but I get Compile-error, stating that an "=" is expected in the end.

This is my code that involves the date so far:

Dim InvoiceDate As Date
'Format(Invoicedate, "YYYY-MM-DD")
InvoiceDate = fldInvoiceDate.Value

As for the format part, I have seen it elsewhere in a somewhat different way:

Format(Date, "YYYY-MM-DD") = InvoiceDate

Using this code, I got an error-message stating that an object was required.

Hope this is enough info.

 

[SkipVought]

Do you understand that DATE FORMAT and DATE VALUE are two different things?

What do you mean by, "The logic for the application that in turn will read this file, expects a "YYYY-MM-DD"-format." What kind of application is that? Is it a MAINFRAME application, another MS application? WHAT?

The Format function returns a STRING and not a DATE. So when you declare InvoiceDate as DATE and then try to assign a STRING, there's a problem.

The issue that you must resolve first is the question that I posed above. That is the watershed issue.





Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

ditto Skip but to correct the error:

Dim InvoiceDate As Date
InvoiceDate = Format(fldInvoiceDate.Value, "YYYY-MM-DD")


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"System date format is "DD/MM-YYYY".

"Without changing system date format, but within the macro, how can I convert the present "DD/MM/YYYY" to "YYYY-MM-DD""

"The date is entered by the user through a text field in a form"

Is the USER entering date as dd/mm/yyyy or DD/MM-YYYY? Either way, you can parse as DMY, as stated above.

That's ONE question.

The NEXT question is regarding the Format function

Dim InvoiceDate As Date
InvoiceDate = Format(fldInvoiceDate.Value, "YYYY-MM-DD")

All this does is CONVERT your fldInvoiceDate.Value TEXT to a DATE (which is a NUMBER), and has nothing at all to do with any particular Date Format.

What is the APPLICATION that will need this Date Value?





Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

and to illustrate the point regarding the Format function and your InvoiceDate, run this...

Sub Test()
Dim InvoiceDate As Date
InvoiceDate = Format(fldInvoiceDate.Value, "YYYY-MM-DD")
msgbox Format(fldInvoiceDate.Value, "YYYY-MM-DD") & " " & InvoiceDate 
end sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CDebet]

Worksheets("Utdata").Range("A1").Offset(r, 0).Value = "<InvoiceDate>" & Format(fldInvoiceDate.Value, "YYYY-MM-DD") & "</InvoiceDate>"


The code above did it! Based on your help, I didn't need the InvoiceDate variable.

Skip: I see your point in your last message. As the output of my macro is a xml-file I do not see the relevance of which application the which shall read the file. Either way, it is set to interpretate the date in a given position as YYYY-MM-DD.

Being a self-learned "programmer" from trial and error, and sometimes with the help of forums such as this, I admit that I have somewhat limited knowledge about the structure in the VB-language.

You guys gave great help in a short time. For that I thank you. This problem was the last major one in my macro.