MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Custom Function returnig a Date Value

Posted by Ernst Glatzer on May 11, 2001 2:05 AM


i have a custom function returning a date value like
this one:

Function MyDate() as Date
End Function

and i want it to change the NumberFormat of the Cell in
which i put it to 'Date'. i have tried
Application.Caller.NumberFormat = "yyyy-mm-dd"
but this doesn't work.
When i put =MyDate() into Cell A1, it shows 37022.
I don't want to change the Format manually - i wnt to have it done by the function (macro).

Can anybody help?

Posted by Ivan Moala on May 11, 2001 7:05 AM

If all your "standard data" begins GE... how about sorting the whole sheet alphabetically and then all the GE rows will be together and the rows with the odd text will be together, you can then block remove the rows with the odd text.

If the rows with the standard data have to be in the order they currently are, add an extra column and number from 1 to howver many for each row before you sort the list, then you can resort the whole list by this new column to return it to it's original order once you've deleted the extra rows.

hope that's clear