Due Date by Quarter

TPALJA

I need a formula that populates the due date based on the date that its approved. I want the date to be at the end of each quarter

prime_nathaniel

@TPALJA

Note that I changed my column name to dateApproved so you will need to rename it in the formula.

=IF(MONTH(dateApproved@row) < 4, DATE(YEAR(dateApproved@row), 3, 31), IF(MONTH(dateApproved@row) < 7, DATE(YEAR(dateApproved@row), 6, 30), IF(IF(MONTH(dateApproved@row) < 10, DATE(YEAR(dateApproved@row), 9, 30), DATE(YEAR(dateApproved@row), 12, 31)))))

Solution explained:
You want to define your use cases for logic as Q1, Q2, Q3, Q4, do this this you can infer quarter based on month.

So month(date) 1-3 will be Q1, 4-6 is Q2, etc, etc

The only thing left after that is to replace Q1-4 with the last day of each of those months and set for a dynamic year value based on the deadline.

date(y,m,d)
date(year(dateApproved@row),1,31) = Jan 31st of the year in your date approved column. Repeat for Q2 to Q4.

prime_nathaniel

@TPALJA

Note that I changed my column name to dateApproved so you will need to rename it in the formula.

=IF(MONTH(dateApproved@row) < 4, DATE(YEAR(dateApproved@row), 3, 31), IF(MONTH(dateApproved@row) < 7, DATE(YEAR(dateApproved@row), 6, 30), IF(IF(MONTH(dateApproved@row) < 10, DATE(YEAR(dateApproved@row), 9, 30), DATE(YEAR(dateApproved@row), 12, 31)))))

Solution explained:
You want to define your use cases for logic as Q1, Q2, Q3, Q4, do this this you can infer quarter based on month.

So month(date) 1-3 will be Q1, 4-6 is Q2, etc, etc

The only thing left after that is to replace Q1-4 with the last day of each of those months and set for a dynamic year value based on the deadline.

date(y,m,d)
date(year(dateApproved@row),1,31) = Jan 31st of the year in your date approved column. Repeat for Q2 to Q4.

TPALJA

@prime_nathaniel thank you for providing it worked perfectly

prime_nathaniel

@TPALJA you are very welcome