ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Howw can I make a function return a date in date format (https://www.excelbanter.com/excel-programming/438659-howw-can-i-make-function-return-date-date-format.html)

Jan Kronsell
Howw can I make a function return a date in date format
 
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
Howw can I make a function return a date in date format
 
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

Jan Kronsell
Howw can I make a function return a date in date format
 
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



RB Smissaert
Howw can I make a function return a date in date format
 
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

Dave Peterson
Howw can I make a function return a date in date format
 
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

Jan Kronsell
Howw can I make a function return a date in date format
 
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



RB Smissaert
Howw can I make a function return a date in date format
 
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

Jan Kronsell
Howw can I make a function return a date in date format
 
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



Gord Dibben
Howw can I make a function return a date in date format
 
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



Jan Kronsell
Howw can I make a function return a date in date format
 
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

RB Smissaert wrote:
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



Jan Kronsell
Howw can I make a function return a date in date format
 
Thanks.

Jan

Gord Dibben wrote:
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



Dave Peterson
Howw can I make a function return a date in date format
 
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

Jan Kronsell
Howw can I make a function return a date in date format
 
Thanks. I have to teach them to format the results as date.

Jan

Dave Peterson wrote:
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




All times are GMT +1. The time now is 11:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com