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

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

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
 
Try something like this:

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

RBS
 
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
 
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
 
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
 
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
 
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
 
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
 
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.
 
Back
Top