Dates and Strings: Last day of the quarter - string "Mar 04" to #5/1/2004# ?????
 

How do I get from the string "Mar 04" to a date format such a
#5/1/2004#

I am trying to get the last date of the quarter that a string such a
"Mar 04" falls in so that I can create quarterly and annual totals.
The string source is a mainframe downloaded text file so I have n
control over it.

I am using the function below which works if you pass in #5/4/2004#.

How do I get from the string "Mar 04" to a date format such a
#5/1/2004#

Dates are Killing me! Thanks for the help, Craigm

=====================================
Function GetLastQuarterEnd(Optional ByVal dtDate As Date) As Date
'Function GetLastQuarterEnd()
'Returns a date equal to the day on which the last calendar quarte
ended.
'dtDate is optional and the current date is used if dtDate is no
supplied.
'If dtDate is a quarter end date, the previous quarter end date i
returned.

Dim lLastQtrMnth As Long
Dim dtLastQtrDay As Date

'If no argument supplied, use today
If dtDate = 0 Then
dtDate = Now
End If


'Calc the month the last quarter ended except for quarter 1
'lLastQtrMnth = (Round((Month(dtDate) / 3) + 0.49, 0) - 1) * 3

'Changinging the -1 to -0 makes it return the current quater en
date cmc
lLastQtrMnth = (Round((Month(dtDate) / 3) + 0.49, 0) - 0) * 3

If lLastQtrMnth = 0 Then
dtLastQtrDay = DateSerial(Year(dtDate) - 1, 12, 31)
Else
dtLastQtrDay = DateSerial(Year(dtDate), lLastQtrMnth + 1, 0)
End If
GetLastQuarterEnd = dtLastQtrDay
'MsgBox (dtLastQtrDay)
End Function
================================

--
Craig
-----------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...fo&userid=2438
View this thread: http://www.excelforum.com/showthread.php?threadid=39215

Dates and Strings: Last day of the quarter - string "Mar 04" to #5/1/2004# ?????
 

dates are stored as Date serial numbers. so try

Dim dtDate as Date
dtDate = cdate("Mar 04")

however,
? format(#5/4/2004#,"mmm dd, yyyy")
May 04, 2004

so I am not sure how that relates to Mar 04

--
Regards,
Tom Ogilvy


"Craigm" wrote in
message ...

How do I get from the string "Mar 04" to a date format such as
#5/1/2004#

I am trying to get the last date of the quarter that a string such as
"Mar 04" falls in so that I can create quarterly and annual totals.
The string source is a mainframe downloaded text file so I have no
control over it.

I am using the function below which works if you pass in #5/4/2004#.

How do I get from the string "Mar 04" to a date format such as
#5/1/2004#

Dates are Killing me! Thanks for the help, Craigm

=====================================
Function GetLastQuarterEnd(Optional ByVal dtDate As Date) As Date
'Function GetLastQuarterEnd()
'Returns a date equal to the day on which the last calendar quarter
ended.
'dtDate is optional and the current date is used if dtDate is not
supplied.
'If dtDate is a quarter end date, the previous quarter end date is
returned.

Dim lLastQtrMnth As Long
Dim dtLastQtrDay As Date

'If no argument supplied, use today
If dtDate = 0 Then
dtDate = Now
End If


'Calc the month the last quarter ended except for quarter 1
'lLastQtrMnth = (Round((Month(dtDate) / 3) + 0.49, 0) - 1) * 3

'Changinging the -1 to -0 makes it return the current quater end
date cmc
lLastQtrMnth = (Round((Month(dtDate) / 3) + 0.49, 0) - 0) * 3

If lLastQtrMnth = 0 Then
dtLastQtrDay = DateSerial(Year(dtDate) - 1, 12, 31)
Else
dtLastQtrDay = DateSerial(Year(dtDate), lLastQtrMnth + 1, 0)
End If
GetLastQuarterEnd = dtLastQtrDay
'MsgBox (dtLastQtrDay)
End Function
=================================


--
Craigm
------------------------------------------------------------------------
Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=392157