Excel formula to get Half-Yearly, Quarterly and Yearly date from a given Date

Use of date and time is common in Excel and we often add a column or two showing date in our worksheet. We use dates in our reports, day-to-day transactions and more. Here in this post, I am sharing a simple Excel formula to get half-yearly, quarterly and yearly date from a given date.

Excel formula to get Half-yearly, quarterly and yearly date from a given date

More Excel Formulas

1) A Simple formula Calculate the Age in Excel using the Year.
2) How to use IF() and Nested IF() Functions in Excel
3) Create Named Cells or Named Range in Excel and Use the Names in Formula or for Data Validation

Let us assume, I have a column named transaction date (see the image above) in my worksheet. I want to show a due date (I’ll pick up my consignment on the due date or collect money from client, whatever), which can be 6 months (or half-yearly) from the transaction date or the due date can be after 3 months (or quarterly) from the trans date.

Here’s the formula to do this. I am using a dd/mm/yyyy format for the dates in the examples here. Please check the date format in your Excel worksheet.

I have 3 columns in Sheet1. A transaction date, followed by a dropdown list to select terms (like half-yearly, quarterly and yearly) and finally the column for due date.

Note: If you don’t know how to add or remove a DropDown list in Excel, read this.

Formula to get “Yearly” date in Excel

=DATE(YEAR(A2), MONTH(A2) + 12, DAY(A2))

Copy and paste the formula in the 3rd column (under Due Date column).

The DATE() function takes three parameters, that is, a year, a month and a day. I am passing the Year, month and day from the 1st column (Transaction Date or A2) and adding 12 to the MONTH() function to get a date after 12 months.

Use the same formula to get half-yearly and quarterly dates. Simply add 6 and 3 to the MONTH.

Formula to get “Half-yearly” date in Excel

=DATE(YEAR(A2), MONTH(A2) + 6, DAY(A2))

Formula to get “Quarterly” date in Excel

=DATE(YEAR(A2), MONTH(A2) + 3, DAY(A2))

Simple isn’t it. Now let’s combine the formulas together using Nested IF statements, so we can automatically get the date based on our selection from the Terms dropdown list.

Date Formula using Nested IF

Copy this formula and paste it in the 3rd column (under Due Date). Click on the column again and drag it down to add the formula in multiple rows in the same column.

=IF(A2 <> "", DATE(YEAR(A2), MONTH(A2) + IF(B2="YEARLY", 12, IF(B2="HALF YEARLY", 6, IF(B2="QUARTERLY", 3, 1)) ), DAY(A2)), "")


Note: I am using a dd/mm/yyyy format for the dates in the examples here. Please check the date format in your Excel worksheet.

The first IF checks if the transaction date column is not empty. Next, I have added the DATE() function with multiple IF statements.

Excel formula using DATE() function with Nested IF statements

That’s it. Thanks for reading.

Previous - Show or hide an entire Column on cell click in Excel using VBA macroNext - Excel VBA - Dynamically create controls in UserForm and save data in Ms-Access


Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!