Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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)
0
chrisamuel
Asked:
chrisamuel
1 Solution
 
Jokra_the_BarbarianCommented:
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..
0
 
Stephen_PerrettCommented:
Try:

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

Steve
0
 
Rey Obrero (Capricorn1)Commented:
you can simply use

quarter: Format([DateReceived], "yy0q")    ' this will give you  0501 or

quarter: Format([DateReceived], "yyyy0q")    ' this will give you  200501 or

quarter: Format([DateReceived], "yyyy-0q")    ' this will give you  2005-01 or

quarter: Format([DateReceived], "yyyy-q")    ' this will give you  2005-1 or
0
 
chrisamuelAuthor Commented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now