asked on

rolling quarterly showing only 4 quarters

I'm using this for a quarterly date function and it seems to be working but I'm off a number or two where it says 2005-Jan. Just wanted to make sure that this isn't messing up between year changes. Also how would I make this a rolling quarterly showing only 4 quarters and formated with just numbers (1,2,3,4) and not the confusing Month names?

Quarter: Year([DateReceived]) & "-" & Left(MonthName(Format([DateReceived],"q")*3-2),3)

Jokra_the_Barbarian

Maybe you could do something like this:

Year([DateReceived]) & "Q" & CInt(Format([DateReceived], "q"))

This would produce: 2004Q1, 2004Q2, 2004Q3, 2004Q4, 2005Q5, etc..

In this format, it would be easy to group, sort, etc..

Stephen_Perrett

Try:

Quarter: Year([DateReceived]) & "-" & Format([DateReceived],"q")

Steve

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chrisamuel

ASKER

I actually had a similar idea capricorn with this:
Quarter: Format([DateReceived],"yyyy" & "-" & "q") but still am wondering if the year changes have anything to do with the numbers being off by one between quarter 4 2004 and quarter 1 2005. I know that it's a date/time field so that might have something to do with it. Also I put this in the where clause for DateReceived and am wondering if this is causing any problems.
>=((Date()-Month(Date())+1)-390)

Either way, I'll just do a manual check to see what's going on, I guess.