Hi all:

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.

Any thoughts?

Thanks,
-sd

I don't see how your syntax falls into this: DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

Thanks for the feedback.

I just tried:



No luck there. Please offer a suggestion, if you know where I am going wrong.

It's appreciated,
-sd

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.

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.

I don't want to seem obtuse, but...

Is there a way that I can set the NLS API setting? If so how?

-sd

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

IssueQuarter: datepart("q",[IssueDate]-28)

should work, but

IssueQuarter: datepart("q",DateSerial(year([IssueDate]), month([IssueDate]), day([IssueDate])-28))

will almost definitely work, although I've tested neither one.