Date conversion issue... again

[Guest]

Hello all,

I have a file that contains dates. I know that for example "12.10.2004"
means "December 10th, 2005", however Excel treats as "Oct. 12th 2004".
Nothing works: converting the format of the cells doen't help, the
TEXT(A1;"mm.dd.yyyy") doesn't do anything because it works on the serial
number instead

Any suggestions?

thanks a lot

 

[R.VENKATARAMAN]

in some other empty cell (without any formt) type this
=(SUBSTITUTE(A1,".","/"))+0
you may get the date number
format it as you like

do you get what you want;.

 

[Guest]

Hi,

You can use the following function to convert the values:

Function ccDate(myDate As Date) As Date

dDate = CStr(myDate)

myValues = Split(dDate, "/")

myMonth = CInt(myValues(0))
myDay = CInt(myValues(1))
myYear = CInt(myValues(2))

ccDate = DateSerial(myYear, myMonth, myDay)

End Function


I am recruting new members for my forum below... The site went live this
weekend and if you wish to take part, please, join me there so that we can
exchange some ideas.

 

[Guest]

I only noticed that you used a "." instead of "/" in the date. In this case,
change the following line:

myValues = Split(dDate, ".")

Alternatively, you can use an extra argument so that you can indicate the
separator.

 

[Roger Govier]

you could try
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
and format the cell with the date format you prefer

 

[Guest]

Hi
I never used such a function before... how do I proceed?...

thanks

 

[RagDyeR]

Check out what format is in your Windows settings.

<Start> <ControlPanel> <Regional Settings>

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hello all,

I have a file that contains dates. I know that for example "12.10.2004"
means "December 10th, 2005", however Excel treats as "Oct. 12th 2004".
Nothing works: converting the format of the cells doen't help, the
TEXT(A1;"mm.dd.yyyy") doesn't do anything because it works on the serial
number instead

Any suggestions?

thanks a lot