Week Number from Date for Fiscal Year (1 Viewer)

Garmani

Registered User.
Local time
Today, 08:57
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

MVP / VIP
Local time
Today, 15:57
Joined
Jan 14, 2017
Messages
18,186
WeekNumber=DateDiff("w",#9/1/2019#,me.datefield)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
19,169
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, 11:57
Joined
Jan 23, 2006
Messages
15,364
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, 08:57
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
19,169
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, 08:57
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

  • q_DHL_SL_Availability.xlsx
    208.5 KB · Views: 264

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
19,169
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

  • q_DHL_SL_Availability (1).zip
    203.6 KB · Views: 267

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:57
Joined
May 21, 2018
Messages
8,463
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, 15:57
Joined
Sep 21, 2011
Messages
14,044
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
19,169
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, 10:57
Joined
May 11, 2011
Messages
11,611
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, 11:57
Joined
May 21, 2018
Messages
8,463
Every year spans 53 weeks. Some span 54
Just like a month can span from 4 to 6 weeks.

Six weeks
 

Attachments

  • 6weeks.jpg
    6weeks.jpg
    31.3 KB · Views: 641

Users who are viewing this thread

Top Bottom