You could return text/string instead of a real date.

Option Explicit
Function CprTilDato2(cpr As String) As String

dim myDate as date

Dim bytcent As String
Dim bytcpryear As String

bytcent = "20"
bytcpryear = "10"

'calculation for date
myDate = dateserial(bytcent & bytcpryear, _
Mid(cpr, 3, 2), _
Left(cpr, 2))

CprTilDato2 = Format(myDate, "dd-mm-yyyy")

End Function

But this returns text--not a real date. It won't be much good for date
arithmetic--without parsing the string.


Jan Kronsell wrote:

If I understand you correctly, what you are sayíng is, that if I call the
function from a worksheet, I cannot format the result from with the code?

Jan

Dave Peterson wrote:
Format the cell as a date.

If the function is NOT called by a worksheet, you can add the
formatting in the code.

Option Explicit
Function CprTilDato(cpr As String) As Date

Dim bytcent As String
Dim bytcpryear As String

bytcent = "20"
bytcpryear = "10"

CprTilDato = DateSerial(bytcent & bytcpryear, _
Mid(cpr, 3, 2), _
Left(cpr, 2))

End Function

Sub Testme01()

Dim myStr As String
myStr = "2001"

with activesheet.range("A1")
.numberformat = "dd-mm-yyyy"
.value = CprTilDato(myStr)
end With
End Sub



Jan Kronsell wrote:

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

--

Dave Peterson