Week Number from Date for Fiscal Year (1 Viewer)

Garmani

Registered User.
Local time
Today, 12:41
Joined
Sep 23, 2019
Messages
18
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
 

isladogs

CID Moderator
Staff member
Local time
Today, 20:41
Joined
Jan 14, 2017
Messages
13,141
WeekNumber=DateDiff("w",#9/1/2019#,me.datefield)
 

arnelgp

error reading drive A:
Local time
Tomorrow, 03:41
Joined
May 7, 2009
Messages
9,280
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
        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

Super Moderator
Staff member
Local time
Today, 15:41
Joined
Jan 23, 2006
Messages
13,077
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.
Local time
Today, 12:41
Joined
Sep 23, 2019
Messages
18
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

error reading drive A:
Local time
Tomorrow, 03:41
Joined
May 7, 2009
Messages
9,280
is it my function?

you can call it in a query:

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

Garmani

Registered User.
Local time
Today, 12:41
Joined
Sep 23, 2019
Messages
18
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
 

Attachments

arnelgp

error reading drive A:
Local time
Tomorrow, 03:41
Joined
May 7, 2009
Messages
9,280
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

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:41
Joined
May 21, 2018
Messages
3,115
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
Local time
Today, 20:41
Joined
Sep 21, 2011
Messages
5,690
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

error reading drive A:
Local time
Tomorrow, 03:41
Joined
May 7, 2009
Messages
9,280
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
Local time
Today, 14:41
Joined
May 11, 2011
Messages
9,803
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.
Local time
Today, 15:41
Joined
May 21, 2018
Messages
3,115
Every year spans 53 weeks. Some span 54
Just like a month can span from 4 to 6 weeks.

Six weeks
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom