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

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

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

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

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

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