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

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

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
Asked:
slimcutter2k
  • 6
  • 6
1 Solution
 
Gustav BrockCIOCommented:
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
 
slimcutter2kAuthor 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
 
slimcutter2kAuthor 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Gustav BrockCIOCommented:
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
 
slimcutter2kAuthor 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
 
slimcutter2kAuthor Commented:
One more thing our fiscal weeks run Mon thru Sun. Almost there thanks so much for your help so far.
0
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
> our fiscal weeks run Mon thru Sun.

That's what vbMonday is for.

/gustav
0
 
slimcutter2kAuthor 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
 
Gustav BrockCIOCommented:
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
 
slimcutter2kAuthor 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
 
Gustav BrockCIOCommented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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