Mar 20 2006, 12:05 PM
I would like a formula I have written to indicate which quarter a date fell into. I have attempted to set a date as the first day of the first quarter, but have had no luck.
Here's what I tried:
It seems to work, except when I aske it to return something that happens on 04/28/06, which should be in Q1, but is being counted in Q2.
Mar 20 2006, 12:21 PM
I don't see how your syntax falls into this: DatePart(interval, date[,firstdayofweek[, firstweekofyear]])
Mar 20 2006, 12:38 PM
Thanks for the feedback.
I just tried:
No luck there. Please offer a suggestion, if you know where I am going wrong.
Mar 20 2006, 12:47 PM
The firstweekofyear argument has these settings:
Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.
Mar 20 2006, 12:52 PM
Where I think you are going wrong is that the firstweekofyear argument doesn't accept a date. It is used primarily to extract the week. Once you use q as the interval argument, the optional arguments are ignored.
It looks like what you want is to base your quarters on a different scale (i.e. Q1 = Feb-Apr, Q2 = May-Jul, etc.)
For that I beleive you will have to do a custom function.
Mar 20 2006, 01:03 PM
I don't want to seem obtuse, but...
Is there a way that I can set the NLS API setting? If so how?
Mar 21 2006, 06:29 AM
Judging by the function you've used there, your year is starting 28 days after the calendar year...so you could simply subtract 28 days from the Issue date to make sure that it's in the right quarter? So
should work, but
IssueQuarter: datepart("q",DateSerial(year([IssueDate]), month([IssueDate]), day([IssueDate])-28))
will almost definitely work, although I've tested neither one.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here