📆Start & End of Quarters from any Date

For the purpose of evaluating financial data in Excel, have you ever pondered how to determine the start or end date of quarters from any random dates? Today, let's talk about how.

The methods to determine the start and end dates of the quarters in which the transaction date falls are as follows:

1. Using Power Query (M Functions)

2. Using Excel Functions

1️⃣ Power Query

For this, we are going to use the Custom Column option available under Add Column tab of the Power Query editor.

Steps to be followed:

1. Select the Data (Date of Transactions)

2. Navigate to the Data tab » Get & Transform Data group » From Table/Range

3. Create a Table (Rename it if you want)

4. Power Query Editor will be opened, rename the Query if you want to (I have changed the same into Quarter Dates)

5. Change the Type of data into Date format.

6. Navigate to Add Column tab » General group » Custom Column

7. Custom Column pop-up window will be opened, name the column as “Start of Quarter” and insert the M Function “=Date.StartOfQuarter([Date of Transaction])” and press OK.

8. Do the exact procedures for the “End of Quarter” except replace End in the formula.

9. Change the type of data into Date format

10. Navigate to the Home tab » Close group » Close and Load to » Select the destination location for loading the table.

=Date.StartOfQuarter([Date of Transaction])
=Date.EndOfQuarter([Date of Transaction])

Advantages 👍🏻

1. Works Dynamically when adding or modifying the data in the original table. After adding or modifying the existing data, hit the refresh option (or) Alt + F5 in the Splitted data table it will fetch you the updated data containing the modified data.

2. Initial Lengthy Process and subsequent easy access to the functionality, this option may consume some time in setting up the initial query but can be easily accessed thereafter by just hitting the refresh option.

2️⃣ ƒ Excel Functions:

Start date of the Quarter:

Let us decode the above formula:

The DATE function will return you the date from inputs of year, month, and date as syntax.

Syntax:

1. Year: we used the YEAR function in this syntax to extract the year from the date of the transaction.

2. Month: we used the FLOOR.MATH & MONTH to extract the start month of the quarter.

3. Day: this denotes the day of the month, we inserted 1 for the same (to return 1st day of the month).

End date of the Quarter:

Let us decode the above formula:

The EOMONTH function will return you the Month end date of the same or subsequent months.

Syntax:

1. Start Date: the date for which we have to find the month-end date

2. Months: 0 means the same month, 1 means the next month, and so on

For finding the no. of months we are using the MOD & MONTH functions.

---

That’s from my side for today guys, see you in the next newsletter, and subscribe to our newsletter it’s free and most importantly useful in your day-to-day work!

---