Formulas of first and last day of each quarterly month.

[KyleJackMorrison]

Hello,

I'm struggling to find a formula that will show the First and Last date of each quarterly month. Basically need to have these dates but for the current year so the year will change if its 2020...

First Quarter:
01/01/2019
31/03/2019
Second Quarter
01/04/2019
30/06/2019
Third Quarter
01/07/2019
30/09/2019
Forth Quarter
01/10/2019
31/10/2019

Thanks in advance.
Kyle

 

[Jeffrey Mahoney]

Use the EOMONTH formula
=EOMONTH(DATE(YEAR(NOW()),3,1),0) to get the last day in march for the current year
=DATE(YEAR(NOW()),1,1) to get the first day in january of the current year

 

[KyleJackMorrison]

Awesome, that's perfect thank you!

 

[James006]

Hi,

In cell A2, just input 1st January
In cell A3 =EOMONTH(DATE(YEAR(A2),ROW(),1),0)

In cell A5 =EDATE(A2,3)
In cell A6 =EOMONTH(DATE(YEAR(A5),ROW(),1),0)

and so on

Hope this will help

 

[Tetra201]

Use the EOMONTH formula...

With DATE(...), you don't need EOMONTH(...):

=DATE(YEAR(NOW()),4,0) will return the last day in March for the current year.

 

[Jeffrey Mahoney]

You would have to get tricky with the last day in December by adding 1 to the year and using: =DATE(YEAR(NOW())+1,1,0)

Not intuitive for me. But I didn't know that, so thank you.

 

[Tetra201]

You would have to get tricky with the last day in December...

Yes, a little bit

=DATE(YEAR(NOW()),13,0)

 

[Jeffrey Mahoney]

WUT! Was that always there? I guess I remember a couple years (maybe longer) ago seeing EOMONTH coming into play and wishing it had always been.

 

[Tetra201]

Yes, it's been there since, at least, Excel 2000.

 

[James006]

OP has the Last day of the Quarter exactly on the Row() number ...