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.

 

[yosegmail]

how about this formula
TEXT(A8,"mm dd yyy")

 

[AlexanderBB]

It works for you Rick ? ? Must be something weird here..

I'm just typing in the immediate window

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

The problem arose in code though... and I had to use

DateIn = .Cells(xRow, "B")
DateIn = Format(DateValue(DateIn), "MM/DD/YYYY")
DateIn = Replace(DateIn, " ", "/")
DateIn = "#" & DateIn & "#"

.cells (xRow, "B") is Formatted as Date Type *14 03 2001

 

[FormR]

It works for me too (XL2013) - but maybe this alternative?

? format("06 Jan 1990","dd\/mm\/yyyy")

 

[AlexanderBB]

This workbook may be the problem... could it be set somehow to a global Date Format and VBA is sticking with it. no matter what ?
I have tried it with a new instance of Excel though, and no better.

Sorry yosegmail, I dont now how to use TEXT. (I tried it and just got a circular reference error!)

 

[Rick Rothstein]

It works for you Rick ? ?

Yes, in both the Immediate Window and in a subroutine executed from the main code window using this code...

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

 

[AlexanderBB]

Yes, formR that does work. Hmmm...

Thanks !

 

[FormR]

Yes, formR that does work. Hmmm...

I wonder if it is something to do with your regional date settings (maybe your system is expecting dates separated by a dot or has a different meaning for a forward slash) the back slash tells the format function to treat the following character literally.

 

[yosegmail]

TEXT(1/06/1996,"mm dd yyy")

 

[MARK858]

TEXT(1/06/1996,"mm dd yyy")

AlexanderBB is using it in VBA so TEXT won't work I'm afraid.