Date conversion issue... again

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

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


Dimmer <[email protected]> wrote in message
news:[email protected]...
> 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

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.

--
Regards
Robert
Find me at www.msofficegurus.com - be part of it!

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.


--
Regards
Robert
Find me at www.msofficegurus.com - be part of it!


"cmart02" wrote:

> 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.
>
> --
> Regards
> Robert
> Find me at www.msofficegurus.com - be part of it!
>

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

--
Regards
Roger Govier
"Dimmer" <[email protected]> wrote in message
news:[email protected]...
> 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

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

thanks

"cmart02" wrote:

> 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.
>
>
> --
> Regards
> Robert
> Find me at www.msofficegurus.com - be part of it!
>
>
> "cmart02" wrote:
>
> > 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.
> >
> > --
> > Regards
> > Robert
> > Find me at www.msofficegurus.com - be part of it!
> >

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!
==============================================

"Dimmer" <[email protected]> wrote in message
news:[email protected]...
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