CODEWITHSUNDEEP
exceldate
trouble
trouble

Reputation: 11

Formula for first day of next quarter excel

Could you please help construct a formula to pull the first day of next quarter from today date in Excel? I tried this one

=DATE(YEAR(D1),3*INT((MONTH(D1)-1)/3)+1,1)

but it actually pulls the first day of the current quarter instead.

Thanks

Upvotes: 0

Views: 8662

Answers (3)

imap
imap

Reputation: 1

You need to ceil the month (as number) to significance of 3, and then deduct 2 from the result. So, you will always catch the first month number from any date.

=DATE(YEAR(C10),CEILING(MONTH(C10),3)-2,1)

Upvotes: 0

bbishopca
bbishopca

Reputation: 296

You had it very close. This formula works.

=DATE(YEAR(D1), ((INT((MONTH(D1)-1)/3)+1)*3)+1, 1)

The only problem is in the fourth quarter it goes to 13 and would take some IF statements to get it to round to the next year

Upvotes: 3

user4039065
user4039065

Reputation:

Try shifting the math manipulations to a standard worksheet function like the CEILING function.

=DATE(YEAR(D2), CEILING(MONTH(D2), 3),1)
=DATE(YEAR(TODAY()), CEILING(MONTH(TODAY()), 3),1)

I'm not entirely clear on whether you wanted to shift ahead if you are in the first month of the quarter.

Upvotes: 1

Related Questions