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.
That’s it. Thanks for reading.