# Week Number from Date for Fiscal Year (1 Viewer)

#### Garmani

##### Registered User.
Hi Everyone - newbie poster

Could you support with the following please-

I have a list of dates dd:mm:yyyy for transactions which I need to allocate a week number to.

Criteria is the Fiscal year starts on the 1st September each year.

Thus 1/9 is always Week 1

Many thanks
Garmani

##### CID Moderator
Staff member
WeekNumber=DateDiff("w",#9/1/2019#,me.datefield)

#### arnelgp

create yet another function:
Code:
``````Option Compare Database
Option Explicit

Public Function fnkFiscalWeek(dte As Variant) As Integer

Dim dteFiscal As Date

If IsEmpty(dte) Then Exit Function
dteFiscal = Dateserial(Year(Date), 9, 1)
If dte < dteFiscal Then
Else
If DateDiff("m", dteFiscal, dte) > 12 Then
End If
End If
'fnkFiscalWeek = ((DateDiff("d", dteFiscal, dte)) \ 7) + 1
fnkFiscalWeek = DateDiff("w", dteFiscal, dte) + 1
End Function``````
NOT tested.

#### jdraw

##### Super Moderator
Staff member
Garmani,

I have a list of dates dd:mm:yyyy for transactions which I need to allocate a week number to.
Are these transactions all in the current fiscal year? If not, how do you handle week number in previous or future fiscal years?

Did you design the date formatting with ":" separator? If so, why?

Last edited:

#### Garmani

##### Registered User.
Thank you for your reply - you may have to guide me with this one as I am struggling to call the function
Thanks
Garmani

#### arnelgp

is it my function?

you can call it in a query:

select [transID], [transDate], fnkFiscalWeek([transDate]) As Week# From yourTransTable;

#### Garmani

##### Registered User.
As per previous attachment - the Deliv#Date field contains the data I want to return a week number. For the Fiscal year commencing 01/09/2018 to 31/08/2019.

Apologies I am just trying to be aligned with your solution

Regards
Garmani

#### Attachments

• 208.5 KB Views: 6

#### arnelgp

I made changes to the function:
Code:
``````Public Function fnkFiscalWeek(dte As Variant) As Integer

Dim dteFiscal As Date

If IsEmpty(dte) Then Exit Function
dteFiscal = DateSerial(Year(Date), 9, 1)
If dte < dteFiscal Then
While dte < dteFiscal
dteFiscal = DateSerial(Year(dteFiscal) - 1, 9, 1)
Wend
Else
If DateDiff("m", dteFiscal, dte) > 12 Then
While DateDiff("m", dteFiscal, dte) > 12
dteFiscal = DateSerial(Year(dteFiscal) + 1, 9, 1)
Wend
End If
End If
'fnkFiscalWeek = ((DateDiff("d", dteFiscal, dte)) \ 7) + 1
fnkFiscalWeek = DateDiff("w", dteFiscal, dte) + 1
End Function``````

#### Attachments

• 203.6 KB Views: 6

#### MajP

##### You've got your good things, and you've got mine.
I do not believe the function works for dates in September in out years.
?fnkFiscalWeek(#9/2/2020#)
53
Believe that should be 1

#### Gasman

##### Enthusiastic Amateur
Would

Code:
``dteFiscal = DateSerial(Year(dte), 9, 1)``
correct it. though it does give 53 for #08/31/2020#

I do not believe the function works for dates in September in out years.
?fnkFiscalWeek(#9/2/2020#)
53
Believe that should be 1

#### arnelgp

correction made. it's an aircode btw.
Code:
``````Public Function fnkFiscalWeek(dte As Variant) As Integer

Dim dteFiscal As Date

If IsEmpty(dte) Then Exit Function
dteFiscal = DateSerial(Year(DATE), 9, 1)
If dte < dteFiscal Then
While dte < dteFiscal
dteFiscal = DateSerial(Year(dteFiscal) - 1, 9, 1)
Wend
Else
If DateDiff("d", dteFiscal, dte) > 365 Then
While DateDiff("d", dteFiscal, dte) > 365
dteFiscal = DateSerial(Year(dteFiscal) + 1, 9, 1)
Wend
End If
End If
'fnkFiscalWeek = ((DateDiff("d", dteFiscal, dte)) \ 7) + 1
fnkFiscalWeek = DateDiff("w", dteFiscal, dte) + 1
End Function``````

#### plog

##### Banishment Pending
Code:
``correct it. though it does give 53 for #08/31/2020#``
Every year spans 53 weeks. Some span 54.

#### MajP

##### You've got your good things, and you've got mine.
Every year spans 53 weeks. Some span 54
Just like a month can span from 4 to 6 weeks.

Six weeks

#### Attachments

• 31.3 KB Views: 29