Solved

# How to Calculate Fiscal Week.

Posted on 2005-04-28
3,772 Views
I am looking for a way to calculate the current fiscal week.

Our fiscal Year Technically starts in Feb. But specifically it ends on the  sun closes to jan 31st whether that day falls before or after Feb and starts that following Monday.

examples:
2003: Began Monday Feb 3rd
2003: Ended Sun Feb 1st

2004: Started Mon Feb 2nd
2004: End Sun Jan 30th

2005: Started Mon Jan31st
2005: Ended Sun Jan 29th

I have a formula the will calculate the fiscal week if the fiscal year starts in Feb 2nd.
DatePart("ww", Now() - 31, vbSunday, vbFirstJan1)

the above function returns 14 and it needs to return 13

I need a function that can give me the fiscal week if I supply a start date and or end date or a similar solution.

I also need a calculation that will give me:
1. the last Sunday in Jan
2. the closest Monday to Feb 1st (regardless if it's in Feb or Jan)

0
Question by:slimcutter2k

LVL 48

Expert Comment

This means that if Feb 1st falls before Friday the FY starts the Monday before, else the Monday after.
Thus, using Monday as the first day of the week, you need to check if the weekday of Feb 1st is <=4.

So:

intYear = 2005
datFYstart = DateSerial(intYear, 2, 1)
intWeekday = Weekday(datFYstart, vbMonday)
If intWeekday <= 4 Then
' Use Monday before.
intOffset = -intWeekday
Else
' Use Monday next.
intOffset = 7 - intWeekday
End If
datFYstart = DateAdd("d", 1 + intOffset, datFYstart)

/gustav
0

LVL 1

Author Comment

ok that works and it gives me the Start of the fiscal year now on do I use that fiscal year start date to me the below formula work? or is there another formula I can use to get the correct fiscal week value?

DatePart("ww", Now() - 31, vbSunday, vbFirstJan1)
0

LVL 1

Author Comment

sorry don't kno what I was typing heres what I meant

ok that works and it gives me the Start of the fiscal year. Now how do I use that fiscal year start date along with the below formula  to get it to work? or is there another formula I can use to get the correct fiscal week value?

DatePart("ww", Now() - 31, vbSunday, vbFirstJan1)
0

LVL 48

Expert Comment

That could be:

Public Function DateFYStart(ByVal intYear As Integer) As Date

Dim datFYstart As Date

datFYstart = DateSerial(intYear, 2, 1)
intWeekday = Weekday(datFYstart, vbMonday)
If intWeekday <= 4 Then
' Use Monday before.
intOffset = -intWeekday
Else
' Use Monday next.
intOffset = 7 - intWeekday
End If
datFYstart = DateAdd("d", 1 + intOffset, datFYstart)

DateFYStart = datFYstart

End Function

Now,

lngFiscalWeek = DateDiff("ww", DateFYStart(Year(datSomeDate)), datSomeDate, vbMonday)

And:

Public Function DateFYEnd(ByVal intYear As Integer) As Date

Dim datFYend As Date

' Subtract one day from start date of next FY.
datFYend = DateAdd("d", -1, DateFYStart(intYear + 1)

DateFYEnd = datFYend

End Function

/gustav
0

LVL 1

Author Comment

I added the code and tried

lngFiscalWeek = DateDiff("ww", DateFYStart(Year(#4/29/2005#)), #4/29/2005#, vbMonday)

I got fiscal Week 12, But This FiscalYear starts Mon-Jan31-04 and were in fiscal week 13. any sugguestions?
0

LVL 1

Author Comment

One more thing our fiscal weeks run Mon thru Sun. Almost there thanks so much for your help so far.
0

LVL 48

Accepted Solution

Yes, the first week is not week 0 but week 1:

Public Function FiscalWeek(Byval datDate As Date) As Long

Dim lngFiscalWeek As Long

lngFiscalWeek = DateDiff("ww", DateFYStart(Year(datDate)), datDate, vbMonday) + 1

FiscalWeek = lngFiscalWeek

End Function

/gustav
0

LVL 48

Expert Comment

> our fiscal weeks run Mon thru Sun.

That's what vbMonday is for.

/gustav
0

LVL 1

Author Comment

ok without sitting here and testing everyday of the fiscal week for three years I think I tested it the best I could.

I only found a few things.

I had a fiscal week return as week 0,-1, and -2. I'm no good with Date functions or Date math so I fixed it the best way I could. Please let me know if there is a better way if not my way will do.

Thank you.

lngfiscalweek = DateDiff("ww", DateFYStart(Year(#2/1/2004#)), #2/1/2004#, vbMonday) + 1
If lngfiscalweek < 0 And lngfiscalweek <> 0 Then
lngfiscalweek = lngfiscalweek + 52
Else
If lngfiscalweek = 0 Then
lngfiscalweek = 52
End If
End If
0

LVL 48

Expert Comment

I see. You need a FiscalYear as well:

Public Function FiscalYear(ByVal datDate As Date) As Integer

Dim intFYyear As Integer

intFYyear = Year(datDate)
If DateDiff("d", datDate, DateFYStart(intFYyear)) < 0 Then
' Fiscal year is before calendar year.
intFYyear = intFYyear - 1
End If

FiscalYear = intFYyear

End Function

Then (note the use of FiscalYear):

Public Function FiscalWeek(ByVal datDate As Date) As Long

Dim lngFiscalWeek As Long

lngFiscalWeek = DateDiff("ww", DateFYStart(FiscalYear(datDate)), datDate, vbMonday) + 1

FiscalWeek = lngFiscalWeek

End Function

/gustav
0

LVL 1

Author Comment

lngFiscalWeek = DateDiff("ww", DateFYStart(FiscalYear(#1/30/2004#)), #1/30/2004#, vbMonday) + 1  still gives me fiscal week 0.

With the If statements I added the function will still work but if you think you can adjust the functions so it would come up 0 or -1's let me know.    Thank You.
0

LVL 48

Expert Comment

You are right. In FiscalYear "<" should of course be ">".

Below is the complete set of functions. Have fun.

/gustav

Public Function DateFYStart(ByVal intYear As Integer) As Date

Dim datFYstart As Date
Dim intWeekday As Integer
Dim intOffset  As Integer

' Nominel start date of fiscal year.
datFYstart = DateSerial(intYear, 2, 1)
intWeekday = WeekDay(datFYstart, vbMonday)
If intWeekday <= 4 Then
' Use Monday before.
intOffset = -intWeekday
Else
' Use Monday next.
intOffset = 7 - intWeekday
End If
' Adjust start date to closest Monday.
datFYstart = DateAdd("d", 1 + intOffset, datFYstart)

DateFYStart = datFYstart

End Function

Public Function DateFYEnd(ByVal intYear As Integer) As Date

Dim datFYend As Date

' Subtract one day from start date of next FY.
datFYend = DateAdd("d", -1, DateFYStart(intYear + 1))

DateFYEnd = datFYend

End Function

Public Function FiscalYear(ByVal datDate As Date) As Integer

Dim intFYyear As Integer

intFYyear = Year(datDate)
If DateDiff("d", datDate, DateFYStart(intFYyear)) > 0 Then
' Fiscal year is before calendar year.
intFYyear = intFYyear - 1
End If

FiscalYear = intFYyear

End Function

Public Function FiscalWeek(ByVal datDate As Date) As Long

Dim lngFiscalWeek As Long

lngFiscalWeek = DateDiff("ww", DateFYStart(FiscalYear(datDate)), datDate, vbMonday) + 1

FiscalWeek = lngFiscalWeek

End Function
0

## Featured Post

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …