Date conversion issue... again

D

Dimmer

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
 
C

cmart02

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.
 
C

cmart02

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.
 
R

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
 
R

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
 
Top