I defined a function in VBA that converts dates keyed in as YYYYMMDD t
be displayed as DD/MM/YYYY using "DateValue" command as well as th
Left, Mid and Right functions

However, excel will only display the dates in the serial numbers.

After the conversion into serial numbers (before 'end function'), i ha
this:


*ActiveCell.Select
Selection.NumberFormat = "dd/mm/yyyy"*

But somehow, it doesn't select that cell to convert into dd/mm/yyy
format.
I have to manually right click the cell and format it as date.

Is there anyway to do this in VBA

--
silves
-----------------------------------------------------------------------
silvest's Profile: http://www.excelforum.com/member.php...fo&userid=1466
View this thread: http://www.excelforum.com/showthread.php?threadid=26728

Hi Silvest,

Not in a function; a function can not change anything in a worksheet. It can
only return a value to the position from which it was called (replace the
call with a value).
You can do what you require in a macro, or you can construct a function
which returns the date as a string. Of course the function call will need an
extra cell.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"silvest" wrote in message
...

I defined a function in VBA that converts dates keyed in as YYYYMMDD to
be displayed as DD/MM/YYYY using "DateValue" command as well as the
Left, Mid and Right functions

However, excel will only display the dates in the serial numbers.

After the conversion into serial numbers (before 'end function'), i had
this:


*ActiveCell.Select
Selection.NumberFormat = "dd/mm/yyyy"*

But somehow, it doesn't select that cell to convert into dd/mm/yyyy
format.
I have to manually right click the cell and format it as date.

Is there anyway to do this in VBA ?


--
silvest
------------------------------------------------------------------------
silvest's Profile:
http://www.excelforum.com/member.php...o&userid=14662
View this thread: http://www.excelforum.com/showthread...hreadid=267289

The only reason the approach you show would not work (given that the cell to
be selected is in the activewindow) that I can think of is if your date
serial is being stored as a string rather than a number.

--
Regards,
Tom Ogilvy

"silvest" wrote in message
...

I defined a function in VBA that converts dates keyed in as YYYYMMDD to
be displayed as DD/MM/YYYY using "DateValue" command as well as the
Left, Mid and Right functions

However, excel will only display the dates in the serial numbers.

After the conversion into serial numbers (before 'end function'), i had
this:


*ActiveCell.Select
Selection.NumberFormat = "dd/mm/yyyy"*

But somehow, it doesn't select that cell to convert into dd/mm/yyyy
format.
I have to manually right click the cell and format it as date.

Is there anyway to do this in VBA ?


--
silvest
------------------------------------------------------------------------
silvest's Profile:
http://www.excelforum.com/member.php...o&userid=14662
View this thread: http://www.excelforum.com/showthread...hreadid=267289