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


"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

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

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

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

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

RBS


"Jan Kronsell" wrote in message
...
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

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

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

Functions return values only.


Gord Dibben MS Excel MVP

On Thu, 21 Jan 2010 00:42:30 +0100, "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

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