Skip to Main Content

Microsoft Tips and Codes

Codes, tips and tricks found over years of experience using Office.

Fiscal/Quarter Year Formulas

Red Diamond

Here is a table where we are looking for calculations to find this year's fiscal cumulative to-date and the previous year's fiscal cumulative to date. Once we have those totals, we can make a pivot table on the data including the columns that are referenced in the lsCode (hidden in this table example):

EndDate lsCode Month Year ThisYr LastYr ThisFiscal LastFiscal
2/29/2012 I.A.1FRM-02 February 2012 6818 8010 48004 62641
2/29/2012 I.A.1FRM-03 February 2012 787 1101 5047 5925

 

Here is one way of finding the fiscal cumulative to-date using SUMIFS (available in Excel 2010 on):

=SUMIFS($F:$F,$A:$A,">="&DATE(YEAR($A2)-1,MONTH($A2)+MOD((6-MONTH($A2)),12),31),$A:$A,"<="&EOMONTH([@EndDate],0),$I:$I,"="&[@lsCode])

$F:$F is the row where the data resides in a 15 row table

$A:$A is the date field, so

$A:$A,">="&DATE(YEAR($A2)-1,MONTH($A2)+MOD((6-MONTH($A2)),12),31)
finds the first day of the current fiscal year, in this case, July 1, 2011

$A:$A,"<="&EOMONTH([@EndDate],0)
finds the last day of the month of the current reporting period. [@EndDate] is the cell (A2 for example) where the specific criteria resides that is being examined. The table this formula came from has been locked in as a table with the Ctrl-T command providing the user with an exact cell reference.

$I:$I,"="&[@lsCode]
finds the unique code identifier for this row and provides the pivot table a way to total only this statistic from July 1 of this fiscal year to the current date.

To find the previous fiscal year, modify the above formula so that it is looking at the previous year (see bold red):

=SUMIFS($F:$F,$A:$A,">="&DATE(YEAR($A2)-2,MONTH($A2)+MOD((6-MONTH($A2)),12),31),$A:$A,"<="&EOMONTH([@EndDate],-12),$I:$I,"="&[@lsCode])

If you don't have the MS Analysis Toolpak installed (courtesy My Online Training Hub):

="FY"&IF(MONTH([@Date])<7,YEAR([@Date]),YEAR([@Date])+1)

Where [@Date] is your date column and 7 in the formula is the starting month of your fiscal year.

For Fiscal Quarters:

="Q"&CHOOSE(MONTH([@Date]),3,3,3,4,4,4,1,1,1,2,2,2)

Where [@Date] is your date field and the sets of numbers match what quarter is displayed starting with January. In this example, January through March is the third quarter (3,3,3), April through June the second quarter (4,4,4) and so on.

MS Analysis Toolpak installed:

="Qtr"&MATCH(MONTH(EDATE(A1,6)),{1,4,7,10})&" FY"&YEAR(EDATE(A1,6))

FISCAL YEAR ONLY:

="FY"&YEAR(EDATE(A1,6))

Explanation: Using YEAR() combined with EDATE(), we can determine the fiscal year. If, for instance, the month of the date in cell A1 is July (month 7) 2009, we go month 7 plus 6 months forward from July 2009 to get an EDATE of January 2010. The YEAR() function generates a year only, so

="FY"&YEAR(EDATE(7/1/2009,6))

BECOMES:

FY2010

FISCAL QUARTER ONLY:

="Qtr"&MATCH(MONTH(EDATE(A1,6)),{1,4,7,10})

Explanation: If, for instance, the month of the date in cell A1 is January (1) we will add 6 to it to get an EDATE of 7. The lookup array {1,4,7,10} provides a relative position for the EDATE. The relative position of EDATE 7 is the 3rd position in the array, or, for our purposes, the 3rd Quarter of the fiscal year.

Date Queries

Red Diamond

The following formulas are ones I have found useful over the years for calculations in MS Access queries. In the following examples:

[Start Date], [End Date] and [Enter Year] are variables prompting for information from the user.

[date] is an already determined date from a field elsewhere in the table. You would have to use the name you have assigned that column for this to work properly.

Your Librarian

Profile Photo
Megan O'Donnell
she / her
Contact:
150 Parks Library
515-294-1670