I understand that I have to format the numer as a date. the problem seems to
be, that no matter how I try, it always returns an unformated value.

I tried this:

d = DateSerial(bytCent & bytCprYear, Mid(cpr, 3, 2), Left(cpr, 2))
MsgBox Format(d, "dd-mm-yyyy")

and it returns 20-01-2010 like you said, but as soon as I try in my UDF,
like

d = DateSerial(bytCent & bytCprYear, Mid(cpr, 3, 2), Left(cpr, 2))
CprTilDato = Format(d, "dd-mm-yyyy")

I'm back to square one, the function returning 40198 in the cell.

Jan

RB Smissaert wrote:
You will have to move the bits around as I couldn't make out from
your post what is year, month and day.
A date in Excel is an integer number, so to show it has a
recognizable date you will need to format it.
As an example:

Dim d As Date

d = DateSerial(2010, 1, 20)

MsgBox Format(d, "dd/mmm/yyyy")


RBS



"Jan Kronsell" wrote in message
...
Unfortunately it still returns an unformated number. And even more
unfortunate, the number is now wrong. In stead of returning 40198 as
supposed, it returns 40756.

Jan

RB Smissaert wrote:
Try something like this:

CprTilDato = DateSerial(CInt(bytCent & bytCprYear), _
CInt(Left$(cpr, 2)), _
CInt(Mid$(cpr, 3, 2)))

RBS


"Jan Kronsell" wrote in message
...
I have a function like this

Function CprTilDato(cpr As String) As Date

a lot of code here.....

CprTilDato = Left(cpr, 2) & "-" & Mid(cpr, 3, 2) & "-" &
bytCent & bytCprYear

End Function

where
bytCent = 20
bytCprYear = 10
Left(Cpr, 2) = 20
Mid(cpr, 3,2) = 01

I want it to return a date in format dd-mm-yyyy but it returns the
value, like instead of returning 20-01-2010 today, it returns
40198. If I change declaration of the function to

Function CprTilDato(cpr As String) As String

it returns the date allright, but as a string as it should, and
then I can't use the result for calculations.

I can format the cell afterwards of cause, but is'nt it possible to
have the function returning a formated date value?

I tried stuff like
CprTilDato = Format(CprTilDato, "dd-mm-yyyy")
CprTilDato = Format(CDate(CprTilDato), "dd-mm-yyyy")
CprTilDato = CDate(CprTilDato)
CprTilDato = DateSerial(bytCent & bytCprYear, Mid(cpr, 3,
2), Left(cpr, 2))

but so far, no good.

Jan