Determine Fiscal Year (FY) Quarter Based on Dates

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,160
Office Version
  1. 365
Platform
  1. Windows
I have a MS Access table where one column has dates i.e. 12/1/12, 7/15/14 and another column where I need the quarter for each date. The quarters are based on the following months:

Q1 = 10,11,12
Q2 = 1,2,3
Q3 = 4,5,6
Q4 = 7,8,9

Is there a formula that I can put in the Validation Rule (under the Field Properties) or something to have this field be automatically determined? If not, will it only work in a query?? Is there a VBA for this?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I guess since the Table is the source data, I would like to ideally have the quarter determined in the Form section.
 
Upvote 0
Where would I need to put this code in? Under Validation Rule in my Table? In my Form?
 
Upvote 0
You can use the DATEADD and DATEPART functions to return the quarter number for your scenario like this:
Code:
Qtr: DatePart("q",DateAdd("m",3,[MyDate]))
Details on DATEADD: DateAdd Function - Access
Details on DATEPART: MS Access: DatePart Function

In addition to my question above, for the syntax [MyDate] I would need to replace it with the name of the column for my field (Date_of_Award)? Correct?
 
Last edited:
Upvote 0
In addition to my question above, for the syntax [MyDate] I would need to replace it with the name of the column for my field (Date_of_Award)? Correct?
Exactly!:cool:
 
Upvote 0
Where you put it really depends on how you need/plan to use it.
The formula I wrote could be inserted into a query as a Calculated Field (also known as an Expression).
See here for step-by-step instructions on how to do that: Access Tips: Calculating in Queries
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top