Hi,

My company runs on a fiscal calendar that starts on the first monday in
July, and is based on a 13 week quarter (4 weeks, 4 weeks, 5 weeks).
What I need, is a way of relating Date() to the actual fiscal month
(e.g. today is 01/07/05, and is the last day in June in our fiscal
calendar).

I tried many methods, and had hoped that DatePart() with the 'ww' flag
and DatePart() with the 'w' flag (to choose the first monday in July,
and then work our the weeks based on that), however it appears to be
only relative to a January start, and not July.

I could probably do a work around by hardcoding some dates, but that
will only work for a given year, and will require changes each year.

Any help would be appreciated.

Cheers,
Haydn

It's been a while since I've had to work with Fiscal Dates, but there
is a KnowledgeBase artical on using Fiscal Dates in Access. Sadly, I
an't remember the number of it at the moment. Try searching for it at
http://support.microsoft.com/

haydn_llewellyn@yahoo.com.au wrote:[color=blue]
> My company runs on a fiscal calendar that starts on the first monday in
> July, and is based on a 13 week quarter (4 weeks, 4 weeks, 5 weeks).
> What I need, is a way of relating Date() to the actual fiscal month
> (e.g. today is 01/07/05, and is the last day in June in our fiscal
> calendar).
> I tried many methods, and had hoped that DatePart() with the 'ww' flag
> and DatePart() with the 'w' flag (to choose the first monday in July,
> and then work our the weeks based on that), however it appears to be
> only relative to a January start, and not July.
> I could probably do a work around by hardcoding some dates, but that
> will only work for a given year, and will require changes each year.[/color]

Hmm... step 1. get the first monday in july of the given year....
something like this:

Public Function FirstMonday(ByVal intMonth As Integer, intYear As
Integer) As Date
Dim dtDate As Date
dtDate = DateSerial(intYear, intMonth, 1)
Do Until Weekday(dtDate) = vbMonday
dtDate = DateAdd("d", 1, dtDate)
Loop
FirstMonday = dtDate

End Function


Then do all your datediff functions from that. Umm... month# would be
something like take the weeknumber, add 3, and integer divide by 4.
= (intWeekNo+3)\4
and that's the month, right?

Hard coding? Nah! The above function should give you the first monday
of any month of any year. Just plug in the month/year combination you
want...

Child's play! You just gotta get a bit used to VB, that's all.

pietlinden@hotmail.com wrote:[color=blue]
> Hmm... step 1. get the first monday in july of the given year....
> something like this:
>
> Public Function FirstMonday(ByVal intMonth As Integer, intYear As
> Integer) As Date
> Dim dtDate As Date
> dtDate = DateSerial(intYear, intMonth, 1)
> Do Until Weekday(dtDate) = vbMonday
> dtDate = DateAdd("d", 1, dtDate)
> Loop
> FirstMonday = dtDate
>
> End Function[/color]

This can be calculated directly without looping.

New version of NthXDay function:

Public Function NthXDay(N As Integer, D As Integer, dtD As Date) _
As Integer
NthXDay = (7 - WeekDay(DateSerial(Year(dtD), Month(dtD), 1)) _
+ D) Mod 7 + 1 + (N - 1) * 7
End Function

This one-line function has been adjusted to use the default
firstdayofweek value for the WeekDay function.

Usage:

First Monday in July 2005:

NthXDay(1, vbMonday, #7/1/05#) ==> 4

BTW, I'm getting close to being finished with the documentation for the
Easter function solved by C. F. Gauss. I'll probably be done in about
two weeks. I have a few loose ends to tidy up and a few details left
to understand fully. Also, I'm looking into the possibility of a
simpler computation given that much of Gauss' method is for getting the
Sunday following a given date which can be done fairly easily using the
WeekDay function. Part of the bonus of doing all this additional
investigation is that I should be able to calculate moon phases and
other holidays that are based on lunar months. No holiday tables for
me!

James A. Fortune