By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Help with Fiscal Date problem Date(), DateDiff(), DatePart()??

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

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/

ha*************@yahoo.com.au wrote:
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.


Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a

pi********@hotmail.com wrote:
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


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

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.