I have this function:

Function Qtr2Date(QtrDate As String, TimeLimit As Long) As Date
'Change "1Q06" to a date + 1

Dim LtrPos As Long
Dim QtrMonth As Long
Dim QtrYear As String
Dim YrPlusTerm As Long

LtrPos = InStr(UCase(QtrDate), "Q")
QtrMonth = Left(QtrDate, LtrPos - 1)
QtrYear = Mid(QtrDate, Len(QtrDate) - LtrPos + 1, 2)
YrPlusTerm = Abs(QtrYear) + TimeLimit

Select Case QtrMonth
Case 1
Qtr2Date = Format("4/1/" + Str(Right(YrPlusTerm, 2)), "mm/dd/yy")

Case 2
Qtr2Date = Format("7/1/" + Str(Right(YrPlusTerm, 2)), "mm/dd/yy")

Case 3
Qtr2Date = Format("10/1/" + Str(Right(YrPlusTerm, 2)), "mm/dd/yy")

Case 4
Qtr2Date = Format("12/31/" + Str(Right(YrPlusTerm, 2)), "mm/dd/yy")
End Select


End Function

It returns the date serial number, not the formatted date.

The column has mixed data, so I can't format the whole column as a
date.

What am I missing?

Thanks,
David

Hi David

A function returns a value, no formatting included. Unfortulately Excel
makes qualified guesses on formatting when it comes to her own date
functions, but not userdefined date functions.

HTH. Best wishes Harald

"David Sisson" skrev i melding
oups.com...
I have this function:

Function Qtr2Date(QtrDate As String, TimeLimit As Long) As Date
'Change "1Q06" to a date + 1

Dim LtrPos As Long
Dim QtrMonth As Long
Dim QtrYear As String
Dim YrPlusTerm As Long

LtrPos = InStr(UCase(QtrDate), "Q")
QtrMonth = Left(QtrDate, LtrPos - 1)
QtrYear = Mid(QtrDate, Len(QtrDate) - LtrPos + 1, 2)
YrPlusTerm = Abs(QtrYear) + TimeLimit

Select Case QtrMonth
Case 1
Qtr2Date = Format("4/1/" + Str(Right(YrPlusTerm, 2)), "mm/dd/yy")

Case 2
Qtr2Date = Format("7/1/" + Str(Right(YrPlusTerm, 2)), "mm/dd/yy")

Case 3
Qtr2Date = Format("10/1/" + Str(Right(YrPlusTerm, 2)), "mm/dd/yy")

Case 4
Qtr2Date = Format("12/31/" + Str(Right(YrPlusTerm, 2)), "mm/dd/yy")
End Select


End Function

It returns the date serial number, not the formatted date.

The column has mixed data, so I can't format the whole column as a
date.

What am I missing?

Thanks,
David

OIC...

I changed the return value to a string and it seems to behave.

Thanks!

It does, it shows a date-looking text string. But a string is not a date,
you can not use it in date calculations. So it's a question of what's it
for.

Best wishes Harald

"David Sisson" skrev i melding
oups.com...
OIC...

I changed the return value to a string and it seems to behave.

Thanks!