Format a cell with a custom function (Excel 2000 SP-3)

Topic

New Reply

I wrote a function to convert dates from yyyymmdd format to a numeric date (20061128 => 39049) but in addition to creating the date number I want the cell formatting changed to mm/dd/yy. Is there any way to do this automatically from within the function?

Function DateConv(DATE8N) As Date

Dim TextDate As String
TextDate = Mid(DATE8N, 5, 2) & “/” & Right(DATE8N, 2) & “/” & Left(DATE8N, 4)
DateConv = CDate(TextDate)

End Function

Reply | Quote

Replies

No, a user-defined function can only return a value, it cannot modify a cell in any other way. Any code intented to do that will simply be ignored. You’ll have to change the format manually, or write a macro to do so.

Reply | Quote

Hi David
Try the attached workbook. In addition to what you can see there is a hidden sheet which holds a named range. As well, there is code in the workbook.
You should be able to set up the appropriate named range, and also copy the various procedures to your workbook.

Reply | Quote

Interesting solution, but a bit of an overkill for how I would be using the formula. I am intrigued with your ThisWorkbook subs – I’m sure I’ll find a use for them somewhere.

Reply | Quote