Excel Guides

Determining Business Quarters from Dates in Excel

Determining business quarters from dates in Excel can be a bit tricky. There are a few different ways to go about it, and the best method may vary depending on your data and how it's formatted. Here are a few tips to help you get started:

1. Use the QUARTER function

If your data is in a format that includes the year and month for each date, you can use the QUARTER function to quickly determine the quarter for each date. For example, if your data is in cells A1:A5 and looks like this:

1/1/2016
4/15/2016
7/2/2016
10/3/2016
12/31/2016

You can use the following formula to determine the quarter for each date:

=QUARTER(A1)

This will return 1 for the first date, 2 for the second date, 3 for the third date, 4 for the fourth date, and so on. You can then format these cells as Quarter 1, Quarter 2, Quarter 3, or Quarter 4 as needed.

2. Use conditional formatting

Another option is to use conditional formatting to highlight cells based on which quarter they fall in. This can be done by creating a new rule with the following criteria:

=MONTH(A1)<4 (Quarter 1)
=MONTH(A1)>3 AND MONTH(A1)<7 (Quarter 2)
=MONTH(A1)>6 AND MONTH(A1)<10 (Quarter 3)
=MONTH(A1)>9 (Quarter 4)

(Note that you'll need to create a separate rule for each quarter.)

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.