No "/" in Format result

[AlexanderBB]

? Format("06 Jan 1990", "mm/dd/yyyy")
01 06 1990

Why does it not result in 01/06/1990 like its being told to ??

Using Excel 2007.

Thanks.

 

[arthurbr]

Try =TEXT(datevalue("06 Jan 1990"), "mm/dd/yyyy")

 

[AlexanderBB]

TEXT gives me Sub or Function not defined.

 

[MARK858]

Change TEXT back to FORMAT if you are doing it in VBA.

Sub GGG()
[A1] = Format(DateValue("06 Jan 1990"), "mm/dd/yyyy")
End Sub

 

[AlexanderBB]

I'm assigning it to a string variable, not [A1], if that has an effect. I still get no "/" in the result and have to use Replace.
Just checked it does work OK in Excel 2003... so this must be something MS have mucked up ?

 

[Rick Rothstein]

? Format("06 Jan 1990", "mm/dd/yyyy")
01 06 1990

Why does it not result in 01/06/1990 like its being told to ??

Using Excel 2007.

It works for me using Excel 2010.

 

[MARK858]

I'm assigning it to a string variable, not [A1], if that has an effect. I still get no "/" in the result and have to use Replace.
Just checked it does work OK in Excel 2003... so this must be something MS have mucked up ?

Same as Rick with 2010, will test with 2007 this evening if no-one else does before then.

Sub GGG()
Dim a As String
a = Format(DateValue("06 Jan 1990"), "mm/dd/yyyy")
Debug.Print a
End Sub

 

[Rick Rothstein]

Same as Rick with 2010, will test with 2007 this evening if no-one else does before then.

Works fine in my copy of Excel 2007 as well.

Question for the OP... where did that date come from? If you copied it from a webpage, check to see if those spaces are ASCII 32 normal spaces or ASCII 160 non-breaking spaces.

 

[AlexanderBB]

I'd be interested in anyone's 2007 results.. it fails for me in both Access and Excel. Maybe my Office 2007 is having problems....
It seems to make no difference if "DateValue" is included, or not.
I haven't got Excel 2010.

 

[Rick Rothstein]

I'd be interested in anyone's 2007 results.. it fails for me in both Access and Excel. Maybe my Office 2007 is having problems....
It seems to make no difference if "DateValue" is included, or not.
I haven't got Excel 2010.

Did you see Message #8 yet?