Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting a text strings but omitting preceding "A" or "The" | Excel Worksheet Functions | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
Excel 2004 for Mac freezes if I enable "drawing" toolbar | Excel Discussion (Misc queries) | |||
Compile Error in Excel 2004 when Inputbox contains "VBCRLF" | Excel Discussion (Misc queries) |