CODEWITHSUNDEEP
exceldateexcel-formulaceil
Ricardo Limon
Ricardo Limon

Reputation: 11

How do I get the last day of the quarter date using Date and ceiling?

I want to get the date that is the last day of the quarter. Can you help me solve this?

I've taken over an old model and trying to understand the logic behind it. The quarter end date is formulated as follows: A2=DATE(Year(A1),CEILING(MONTH(A1),3)+1,0) Where cell A1=2019-07-01. This formula= 2019-09-30 which is the right date, but I'm having a hard time understanding the ceiling, multiplier and +1. I think the +1 is to get September (9) instead of August (8) but I don't understand why 3 as a multiplier returns 8 if it is not a multiplier of 3. Shouldn't it return 6 or 9? Thank you so much for your help!

Upvotes: 0

Views: 10944

Answers (1)

BigBen
BigBen

Reputation: 50008

Let's break this into parts:

  • MONTH(A1): equals 7.
  • CEILING(7,3): equals 9.
  • CEILING(7,3)+1: equals 10.
  • YEAR(A1): equals 2019.
  • DATE(2019, 10, 0): equals 2019-09-30.

Using 0 as the day with DATE is the trick - October "0th" is actually September 30th.

Upvotes: 1

Related Questions