Howw can I make a function return a date in date format

[Jan Kronsell]

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

 

[RB Smissaert]

Try something like this:

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

RBS

 

[Jan Kronsell]

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]

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

 

[Dave Peterson]

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]

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]

As Dave P mentioned, then format the cell as a date.

RBS

 

[Jan Kronsell]

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

 

[Gord Dibben]

Functions return values only.


Gord Dibben MS Excel MVP

 

[Jan Kronsell]

Apparently I have to do it that way, which was actually what I wanted to
avoid, as the users do not understand why my converter does not return a
date as the expect it to do. Then do not necessarily understand the
connection between 40198 and 20-01-2010.

Jan

 

[Jan Kronsell]

Thanks.

Jan

Functions return values only.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

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]

Thanks. I have to teach them to format the results as date.

Jan