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]

Book1
	F	G	H	I
1
2		31/03/2020	30/09/2020
3
4		31/01/2020	31/01/2020
5
Sheet5

Cell Formulas
Range	Formula
G4:H4	G4	=IF((G2-DATE(YEAR(G2),1,31))<(DATE(YEAR(G2),1,31)-G2),DATE(YEAR(G2),1,31),DATE(YEAR(G2),1,31))

 

[chet645]

Thank you CA_Punit, but I would like the formula to round cells G2 and H2 to the nearest January date. So, cell H2 should round to 31/01/2021.

 

[Fluff]

How about

+Fluff New.xlsm
	I	J
7	05/01/2020	05/01/2020
8	02/02/2020	02/01/2020
9	01/03/2020	01/01/2020
10	29/03/2020	29/01/2020
11	26/04/2020	26/01/2020
12	24/05/2020	24/01/2020
13	21/06/2020	21/01/2020
14	19/07/2020	19/01/2021
15	16/08/2020	16/01/2021
16	13/09/2020	13/01/2021
17	11/10/2020	11/01/2021
18	08/11/2020	08/01/2021
19	06/12/2020	06/01/2021
20	03/01/2021	03/01/2021
21	31/01/2021	31/01/2021
Data

Cell Formulas
Range	Formula
J7:J21	J7	=EDATE(I7,IF(MONTH(I7)<7,-MONTH(I7)+1,13-MONTH(I7)))

 

[ER_Neha]

Hello, I tried solving your problem, hope it helps you. just one modification is the row in which you will be extracting the nearest January date, you have to specify the number formatting as the date. other than that the formula will work well, I suppose.

Book1
	A	B
1	10-01-2020	10-01-2020
2	11-01-2021	11-01-2021
3	31-01-2020	31-01-2020
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=IF(MONTH(A1)<7,DATE(YEAR(A1),1,DAY(A1)),DATE(YEAR(A1)+1,1,DAY(A1)))

 

[CA_Punit]

I thought you wanted only 31/01/2020 or 31/01/2021

Anyway i have modified
=DATE(YEAR(IF((I7-DATE(YEAR(I7),1,1))>=(DATE(YEAR(I7)+1,1,1)-I7),DATE(YEAR(I7)+1,1,31),DATE(YEAR(I7),1,31))),1,DAY(I7))

 

[Fluff]

A simpler version of my formula from post#4
=EDATE(I7,IF(MONTH(I7)<7,1,13)-MONTH(I7))

 

[ER_Neha]

How about

+Fluff New.xlsm
	I	J
7	05/01/2020	05/01/2020
8	02/02/2020	02/01/2020
9	01/03/2020	01/01/2020
10	29/03/2020	29/01/2020
11	26/04/2020	26/01/2020
12	24/05/2020	24/01/2020
13	21/06/2020	21/01/2020
14	19/07/2020	19/01/2021
15	16/08/2020	16/01/2021
16	13/09/2020	13/01/2021
17	11/10/2020	11/01/2021
18	08/11/2020	08/01/2021
19	06/12/2020	06/01/2021
20	03/01/2021	03/01/2021
21	31/01/2021	31/01/2021
Data

Cell Formulas
Range	Formula
J7:J21	J7	=EDATE(I7,IF(MONTH(I7)<7,-MONTH(I7)+1,13-MONTH(I7)))

Learned the new trick today. Thank you so much.

 

[Dave Patton]

T202008a.xlsm
	A	B
1	7-Aug-20	01-Jan-2021
2		01-Jan-2021
3
5a

Cell Formulas
Range	Formula
B1	B1	=DATE(YEAR(A1)+(YEARFRAC(DATE(YEAR(A1),1,1),A1)>0.5),1,1)
B2	B2	=DATE(YEAR(TODAY())+(YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY())>0.5),1,1)

 

[ER_Neha]

A simpler version of my formula from post#4
=EDATE(I7,IF(MONTH(I7)<7,1,13)-MONTH(I7))

Hello, EDATE will also require to convert the number formatting manually to date, is there any function which in spite of providing the serial number of date provides the exact date.