[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 4864

# How to Calculate Fiscal Week.

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
slimcutter2k
• 6
• 6
1 Solution

CIOCommented:
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

Author Commented:
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

Author Commented:
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

CIOCommented:
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

Author Commented:
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

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

CIOCommented:
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

CIOCommented:
> our fiscal weeks run Mon thru Sun.

That's what vbMonday is for.

/gustav
0

Author Commented:
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

CIOCommented:
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

Author Commented:
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

CIOCommented:
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

• 6
• 6
Tackle projects and never again get stuck behind a technical roadblock.