Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarterly date format issues
Hi,
I am writing writing a subroutine which works with a series of quarterly dates (e.g. "Q3-08, Q4-08, Q1-09, Q2-09,..." in a single row. I had wanted to use CDate in my routine, but I can't get it to work with the date format I am using. Any thoughts? -- Hugo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarterly date format issues
You could use hidden cells - eg put 01/07/2008. 01/10/2008, 01/01/2009 in row 1 and then ="Q"&int(month(A1)-1)/3)+1&" "&year(A1) in cell A2 etc (assuming you want calendar quarters and not financial ones) "Hugo" wrote: Hi, I am writing writing a subroutine which works with a series of quarterly dates (e.g. "Q3-08, Q4-08, Q1-09, Q2-09,..." in a single row. I had wanted to use CDate in my routine, but I can't get it to work with the date format I am using. Any thoughts? -- Hugo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarterly date format issues
Assuming you have assigned the "quarter date" to a variable named CellVal, this statement will assign the quarter's start date to the indicated variable... QuarterDate = CDate("1-" & Choose(Mid(CellVal, 2, 1), _ "Jan-", "Apr-", "Jul-", "Oct-") & Right(CellVal, 2)) -- Rick (MVP - Excel) "Hugo" wrote in message ... Hi, I am writing writing a subroutine which works with a series of quarterly dates (e.g. "Q3-08, Q4-08, Q1-09, Q2-09,..." in a single row. I had wanted to use CDate in my routine, but I can't get it to work with the date format I am using. Any thoughts? -- Hugo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarterly date format issues
Try Msgbox "Q" &format(Date,"q-yy") Replace Date() with your date variable.. If this post helps click Yes --------------- Jacob Skaria "Hugo" wrote: Hi, I am writing writing a subroutine which works with a series of quarterly dates (e.g. "Q3-08, Q4-08, Q1-09, Q2-09,..." in a single row. I had wanted to use CDate in my routine, but I can't get it to work with the date format I am using. Any thoughts? -- Hugo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarterly date format issues
Here is another way to do it (again, assuming CellVal contains the text of your "quarter date" cell)... QDate = DateAdd("q", Mid(CellVal, 2, 1) - 1, "1-Jan-" & Right(CellVal, 2)) where I used QDate instead of QuarterDate as the receiving variable because that name is short enough to allow the entire statement to print on a single line (without word wrapping) in your news reader. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Assuming you have assigned the "quarter date" to a variable named CellVal, this statement will assign the quarter's start date to the indicated variable... QuarterDate = CDate("1-" & Choose(Mid(CellVal, 2, 1), _ "Jan-", "Apr-", "Jul-", "Oct-") & Right(CellVal, 2)) -- Rick (MVP - Excel) "Hugo" wrote in message ... Hi, I am writing writing a subroutine which works with a series of quarterly dates (e.g. "Q3-08, Q4-08, Q1-09, Q2-09,..." in a single row. I had wanted to use CDate in my routine, but I can't get it to work with the date format I am using. Any thoughts? -- Hugo |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quarterly date format issues
Hugo,
Now I understood where your question came from..Please check your other post. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try Msgbox "Q" &format(Date,"q-yy") Replace Date() with your date variable.. If this post helps click Yes --------------- Jacob Skaria "Hugo" wrote: Hi, I am writing writing a subroutine which works with a series of quarterly dates (e.g. "Q3-08, Q4-08, Q1-09, Q2-09,..." in a single row. I had wanted to use CDate in my routine, but I can't get it to work with the date format I am using. Any thoughts? -- Hugo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting records by month and year / date format issues | Excel Discussion (Misc queries) | |||
Date Auto-detect & format Issues | Excel Discussion (Misc queries) | |||
Date Format Issues Access to Excel | Excel Discussion (Misc queries) | |||
date format/charting issues | Excel Discussion (Misc queries) | |||
Update a Date Quarterly | Excel Worksheet Functions |