Round Date to Nearest January

[chet645]

Hello Everyone,

I am wondering how to use a dynamic formula that rounds a given date to the nearest January month. For example, If I have two dates that I want to round (3/31/2020 and 10/31/2020), the formula would calculate the fist date as 1/31/2020 and the second date to 1/31/2021.

Your help is much appreciated.

Chet

 

[CA_Punit]

Slightly Shorter than post 6

=DATE(IF((I7-DATE(YEAR(I7),1,1))>=(DATE(YEAR(I7)+1,1,1)-I7),YEAR(I7)+1,YEAR(I7)),1,DAY(I7))

 

[chet645]

Thanks All, for the speedy response. It is much appreciated. I love the conciseness of Fluff's formula. I ultimately wrapped that formula in an EOMONTH function because my model uses end of month convention.

Thanks again!!

 

[Fluff]

Glad we could help & thanks for the feedback.
To get the month end, you can just swap edate for eomonth
=EOMONTH(I7,IF(MONTH(I7)<7,1,13)-MONTH(I7))

 

[Rick Rothstein]

Here is another formula that you can consider...

=DATE(YEAR(G2)+(G2>DATE(YEAR(G2),6,30)),1,31)

 

[Rick Rothstein]

Here is another formula that you can consider...

=DATE(YEAR(G2)+(G2>DATE(YEAR(G2),6,30)),1,31)

Actually, we can shorten this a little (using a variation on the idea Fluff posted in Message #13)...

=DATE(YEAR(A183)+(MONTH(A183)>6),1,31)