Week Number from Date for Fiscal Year

[Garmani]

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

 

[Gasman]

Find the date difference in days and divide by 7?
Add 1 to adjust perhaps?

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/datediff-function

 

[isladogs]

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

 

[arnelgp]

create yet another function:

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
        dteFiscal = DateAdd("yyyy", -1, dteFiscal)
    Else
        If DateDiff("m", dteFiscal, dte) > 12 Then
            dteFiscal = DateAdd("yyyy", 1, dteFiscal)
        End If
    End If
    'fnkFiscalWeek = ((DateDiff("d", dteFiscal, dte)) \ 7) + 1
    fnkFiscalWeek = DateDiff("w", dteFiscal, dte) + 1
End Function

NOT tested.

 

[jdraw]

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?

 

[Garmani]

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]

Yes its your function -
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

 

[arnelgp]

I made changes to the function:

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

 

[MajP]

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]

Would

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.

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]

correct it. though it does give 53 for #08/31/2020#

Every year spans 53 weeks. Some span 54.

 

[MajP]

Every year spans 53 weeks. Some span 54

Just like a month can span from 4 to 6 weeks.

Six weeks