Derive Quarters

[khwaja]

I am using the following formula to list quarters.

Qtr: Format$(DateAdd("q",2,[Finish]),"\Qq yyyy",0,0)


This really works on the premise of month end. However, as we follow an accounting quarter (using July-Jun financial year) which always starts from a Monday and ends on Sunday, I need to amend the formula accordingly. As an example my existing formula reports 1 Oct 2014 as the quarter 2 of FY 2015. In reality, quarter 2 started on 29 Sep 14 as the last Sunday of the month and quarter. I have reproduced a few dates which should be in quarter 1 but my formula shows these to be in quarter 2.

Opening
30-Sep-14
30-Oct-14
29-Sep-14

Will appreciate some help.

 

[namliam]

It is probably easiest in this case to make a table
StartDate - Enddate - Quarter

This then you can use as a simple lookup table to find out which quarter you want to report
Perhaps even a proper dimentional table listing every possible date and every possible lookup
LookupDate - FiscalYear - FiscalQuarter - FiscalMonth - etc...

This would be relatively quick to make this list in excel for 10 years and import it into your database.

I highly doubt 10 oct would be in Q1 and per your description you would want 29/30 sept to be Q2 not Q1 ??? Jul - Aug - Sep would be Q1 right?
To transpose 1/jul/2014 to "Q1-2015" you need to add 5 months not 2 Quarters

Or you can try a custom function, something along the lines off....

Function namliam_QFix(YourDate As Date) As String
    Dim StartQ As Date
    Dim EndQ As Date
    Dim AutoQ As Integer
    AutoQ = Format(DateAdd("m", 5, YourDate), "Q")
    StartQ = DateAdd("q", AutoQ, DateSerial(Year(YourDate), 7, 1))
    EndQ = DateAdd("Q", AutoQ + 1, DateSerial(Year(YourDate), 7, 1)) - 1
    ' Make the monday before be the start
    StartQ = StartQ - Weekday(StartQ, vbMonday) + 1
    EndQ = EndQ - Weekday(EndQ, vbMonday)
    namliam_QFix = AutoQ + 1 & "-" & Year(DateAdd("M", 5, YourDate))
    If DateAdd("m", 5, YourDate) > EndQ Then
        namliam_QFix = AutoQ + 1 & "-" & Year(DateAdd("M", 5, YourDate))
    End If
End Function

 

[plog]

You contradicted yourself in back to back sentences:

In reality, quarter 2 started on 29 Sep 14 as the last Sunday of the month and quarter.

Means 9/30/2014 is in Q2, but then you immediately say:

 I have reproduced a few dates which should be in quarter 1 but my formula shows these to be in quarter 2.

And then say 9/30/2014 should be in Q1. Which is it?

If namlians solution isn't what you want provide me a list of 5 dates all in a row and they quarter they are in. Start with 2 days before Q1 starts and end with 2 days after Q1 begins. If you used a standard calendar this is what I would expect:

Date, Quarter
12/30/2014, 4
12/31/2014, 4
1/1/2015, 1
1/2/2015, 1
1/3/2015, 1

 

[khwaja]

Thank you Namaliam and Plog. The function is great and that needs to be adjusted a little. Plog, I may have mixed up a few things. In nutshell, last sunday in each quarter is the end of the quarter. The following Monday is the start of new quarter. Most of the time, we have an issue in stores opening in end Sept and end June. Using the Namalian's function, I found following anomalies. I have also attached a list of all dates I am interested in highlighting the once which should be in the later or prior quarter.

BuilderComp Qtr Should be
Wednesday, 24 September 2014 2-2015
Friday, 26 September 2014 2-2015
Monday, 29 September 2014 2-2015 2-2015
Tuesday, 30 September 2014 2-2015 2-2015
Wednesday, 15 October 2014 2-2015
Thursday, 16 October 2014 2-2015
Wednesday, 22 October 2014 2-2015
Tuesday, 28 October 2014 2-2015
Tuesday, 24 February 2015 4-2015
Monday, 16 March 2015 4-2015
Wednesday, 25 March 2015 4-2015
Friday, 27 March 2015 4-2015
Friday, 27 March 2015 4-2015
Monday, 30 March 2015 4-2015 3-2015
Tuesday, 31 March 2015 4-2015 3-2015

 

[namliam]

In your excel sheet, how would you arrive at Qtr 5-2014 for Jul 2014?

With a Jul/Jun fiscal year,
30 Jun - 28 Sep would be Q1 - 2014
29 Sep - 28 Dec would be Q2 -2014
etc...

Right?

Found an inconsistancy in my function anyways.... Try this

Function namliam_QFix(YourDate As Date) As String
    Dim StartQ As Date
    Dim EndQ As Date
    Dim AutoQ As Integer
    AutoQ = Format(DateAdd("m", -6, YourDate), "Q")
    StartQ = DateAdd("q", AutoQ - 1, DateSerial(Year(YourDate), 7, 1))
    EndQ = DateAdd("Q", AutoQ, DateSerial(Year(YourDate), 7, 1)) - 1
    ' Make the monday before be the start
    StartQ = StartQ - Weekday(StartQ, vbMonday) + 1
    EndQ = EndQ - Weekday(EndQ, vbMonday)
    namliam_QFix = AutoQ & "-" & Year(DateAdd("M", 5, YourDate))
    
    If YourDate > EndQ Then
        namliam_QFix = AutoQ + 1 & "-" & Year(DateAdd("M", 5, YourDate))
    End If
End Function

 

[khwaja]

Thanks and I apologise for incorrect quarters in July. I was trying to mock up using fill down.

I have now used the revised code. But I still have an issues. I have attached a copy of the dates and the quarters being assigned using the code. I have highlighted where adjustment will be needed. Many thanks for your kind efforts.

Cheers

 

[namliam]

So Sept 29, that IS supposed to be Q2 right???

My function above
?namliam_QFix(#29/09/2014#)
2-2015
?namliam_QFix(#30/09/2014#)
2-2015

Does return 2-2015?

Also
?namliam_QFix(#30/09/2015#)
2-2016

Or am I misunderstanding your requirement?

 

[khwaja]

Thanks. These two dates are appearing in my query as Qtr 1-2015. These should be Qtr 2-2015. This is the result when I ran the function (made slight changes to the function).

?QFix(#29/09/2014#)
Qtr 1-2015

Something is not right if you are getting it as quarter 2.

 

[namliam]

Something is not right if you are getting it as quarter 2.

Something isnt right, but it isnt my function... that atleast for me is working as per expected
Though strictly speaking it should be
?namliam_QFix(#09/29/2014#)
?namliam_QFix(#09/30/2014#)

But for these dates that shouldnt matter.
Can you post part of your db, including your table, some mockup data, query and "your function" ... Then I can have a look at that.

 

[khwaja]

Thanks once again. I have attached a test db with the data, query and function. Thanks for persevering with me.

 

[jdraw]

I tried your database. I get YourDate not defined in the function.

 

[khwaja]

I am referring to this as Buildercomp. This code is slightly amended from what was posted by namaliam.

 

[jdraw]

Perhaps you are but this is the function in your latest QTRs.zip @ post 10.

Function QFix(BuilderComp As Date) As String 'To work out quarters
    Dim StartQ As Date
    Dim EndQ As Date
    Dim AutoQ As Integer
    AutoQ = Format(DateAdd("m", -6, BuilderComp), "Q")
    StartQ = DateAdd("q", AutoQ - 1, DateSerial(Year(BuilderComp), 7, 1))
    EndQ = DateAdd("Q", AutoQ, DateSerial(Year(BuilderComp), 7, 1)) - 1
    ' Make the monday before be the start
    StartQ = StartQ - Weekday(StartQ, vbMonday) + 1
    EndQ = EndQ - Weekday(EndQ, vbMonday)
    QFix = "Qtr " & AutoQ & "-" & Year(DateAdd("M", 5, BuilderComp))

    If [COLOR="Purple"][B][I][COLOR="Red"]YourDate[/COLOR][/I][/B][/COLOR] > EndQ Then
        QFix = "Qtr " & AutoQ + 1 & "-" & Year(DateAdd("M", 5, BuilderComp))
    End If
End Function

YourDate is not defined.

 

[khwaja]

Many thanks for pointing out the error. It has actually addressed the issue to some extent. I was having issues with some dates in Sep 14 but I still have issues for dates in march which should be assigned quarter 4 according to the convention.

Monday, 30 March 2015 Qtr 3-2015
Tuesday, 31 March 2015 Qtr 3-2015

 

[namliam]

Thanks once again. I have attached a test db with the data, query and function. Thanks for persevering with me.

You call this persevering?
This one took a little longer:
http://www.access-programmers.co.uk/forums/showthread.php?t=270362

I strongly advice you tick "Option Explicit" ... Which will force you to have declarations for all you variables and prevent these kinds of issues.
In the VBA edditor go: Extra > Options
On the editor tab tick: Variable Declaration required (the exact name can differ a bit between versions)

Aha I see the issue, not taking into account the second half of the fiscal year would produce problems
30-03-2015 is getting 27-03-2016 as its end of quarter date
Instead of 29-03-2015

So try this:

Public Function QFix(BuilderComp As Date) As String 'To work out quarters
    
    Dim StartQ As Date
    Dim EndQ As Date
    Dim AutoQ As Integer
    AutoQ = Format(DateAdd("m", -6, BuilderComp), "Q")
    If BuilderComp < DateSerial(Year(BuilderComp), 7, 1) Then
        StartQ = DateAdd("q", AutoQ - 1, DateSerial(Year(BuilderComp) - 1, 7, 1))
        EndQ = DateAdd("Q", AutoQ, DateSerial(Year(BuilderComp) - 1, 7, 1)) - 1
    Else
        StartQ = DateAdd("q", AutoQ - 1, DateSerial(Year(BuilderComp), 7, 1))
        EndQ = DateAdd("Q", AutoQ, DateSerial(Year(BuilderComp), 7, 1)) - 1
    End If
    
    ' Make the monday before be the start
    StartQ = StartQ - Weekday(StartQ, vbMonday) + 1
    EndQ = EndQ - Weekday(EndQ, vbMonday)
    QFix = "Qtr " & AutoQ & "-" & Year(DateAdd("M", 6, BuilderComp))

    If BuilderComp > EndQ Then
        QFix = "Qtr " & AutoQ + 1 & "-" & Year(DateAdd("M", 6, BuilderComp))
    End If
End Function

Can probably be coded a little tighter, but I think you should be able to follow this more easily.

 

[khwaja]

I am very grateful for the advice (I have done as suggested) and changes to the code. Yes it was more than persevering and kudos to yourself for being patient. Kind regards to you and others who have contributed.

 

[khwaja]

Hi namliam

I apologise for reverting. I still seem to have an issue as some dates are incorrectly falling into wrong quarter. I have used the following code and attached a spreadsheet which shows what dates should fall into what quarters. Could you kindly see if the code can be improved further? As an example, I had some issues with 30 march 15 which should be in qtr 4. of FY 15.


Public Function QFix(BuilderComp As Date) As String 'To work out quarters

Dim StartQ As Date
Dim EndQ As Date
Dim AutoQ As Integer
AutoQ = Format(DateAdd("m", -6, BuilderComp), "Q")
If BuilderComp < DateSerial(Year(BuilderComp), 7, 1) Then
StartQ = DateAdd("q", AutoQ - 1, DateSerial(Year(BuilderComp) - 1, 7, 1))
EndQ = DateAdd("Q", AutoQ, DateSerial(Year(BuilderComp) - 1, 7, 1)) - 1
Else
StartQ = DateAdd("q", AutoQ - 1, DateSerial(Year(BuilderComp), 7, 1))
EndQ = DateAdd("Q", AutoQ, DateSerial(Year(BuilderComp), 7, 1)) - 1
End If

' Make the monday before be the start
StartQ = StartQ - Weekday(StartQ, vbMonday) + 1
EndQ = EndQ - Weekday(EndQ, vbMonday)
QFix = "FY " & Right(Year(DateAdd("M", 6, BuilderComp)), 2) & " Qtr " & AutoQ

If BuilderComp > EndQ Then
QFix = "FY " & Right(Year(DateAdd("M", 6, BuilderComp)), 2) & " Qtr " & AutoQ
End If
End Function

 

[plog]

I think I finally have a handle on your rules:

A. Fiscal year starts on the Monday prior to July 1, unless July 1 is a Monday in which case the fiscal year starts on 7/1.

B. A Fiscal quarter starts on the Monday prior to the first day of a calendar quarter, unless a calendar quarter is a Monday in which case the fiscal quarter starts on the calendar quarter.

If both of those are correct, then I have 2 functions for you, 1 to determine the Fiscal Quarter and one to determine the Fiscal Year:

Fiscal Quarter:

Public Function get_FiscalQuarter(in_Date) As Integer
    ' returns fiscal quarter a date falls in
    
ret = 0         ' default value which indicates error

If IsDate(in_Date) Then
    ' if in_Date is a date, will calculate Fiscal Quarter
    ret = DatePart("q", in_Date)
    ' fiscal quarter initially set to calendar quarter
    
    If (DatePart("q", in_Date + 7) <> ret) Then
    ' if date occurs in last week of calendar quarter will see if it occurs before last sunday, if not, moves it to next quarter
        
        dt_NextMonth = CDate(Month(in_Date + 7) & "/1/" & Year(in_Date + 7))
    ' gets 1st date of next calendar month after in_Date
    
        If (DateDiff("d", in_Date, dt_NextMonth) <= (Weekday(dt_NextMonth) - 2)) Then ret = ret + 1
    ' if in_Date occurs on or after last Monday in Quarter, sets quarter to next one
        End If
        
    ret = ((ret + 1) Mod 4) + 1
    ' offsets fiscal quarter  because FY starts in July

    End If

get_FiscalQuarter = ret
       
End Function

Fiscal Year:

Public Function get_FiscalYear(in_Date) as Integer
    ' returns fiscal year a date falls in
    
ret = 0         ' default value which indicates error

If IsDate(in_Date) Then
    ' if in_Date is a date, will calculate Fiscal year
    
    ret = Year(in_Date)
    ' fiscal year initially set to calendar year
    
    If Month(in_Date) >= 7 Then ret = ret + 1
    ' if date occurs after 6/30 sets it to next fiscal year
    
    If ((Month(in_Date) = 6) And (get_FiscalQuarter(in_Date) <> 4)) Then ret = ret + 1
    ' if date is in June and calendar quarter isn't Q4, sets to next fiscal year
        
    End If

get_FiscalYear = ret
    
End Function

 

[khwaja]

Thanks for your kind effort. You got it right in terms of when and how we treat quarters and years. In terms of using the code, could you kindly guide me through. I suppose I can use both codes individually. But if I were to derive quarters, how do I get FY as part of the quarter description. The format I am using is for example "FY 16 Qtr 1". Do I need to to use both codes or concatenate with FY field?

I edited this post as I ran the code to check. Looks like I still have the discrepancy as I have highlighted in the attached spreadsheet. I think in terms of your rules, instead of working off Mondays, if you could use the logic that last Sunday in a month is the end of fiscal quarter and year, then you will get this right.

 

[plog]

Yes, you would have to concatenate that yourself using a call to each function:

FYQuarter: "FY " & get_FiscalYear([YourDateField]) & " Qtr " & get_FiscalQuarter([YourDateField])