Date function not rolling to next year

[sheller]

Hi Excel wizards,
I have a date formula that is working in all ways except near year-end, when instead of rolling to the next year, it rolls back to the current year. I feel like I'm just missing one small part of the formula, but can't figure it out. Any help appreciated. Thank you!

Formula in cell I2 of the image is as follows: =DATE(YEAR([@[ES_ETA]]), MONTH(EOMONTH([@[ES_ETA]], (DAY([@[ES_ETA]])>25)+1)),1)

 

[Toadstool]

Hi Sheller,

The YEAR is always that in ES_ETA but you add to the months so the year should change.

I'm not sure why your first two rows should be the 2nd of the month so can you explain or does this do what you ask?

Sheller4.xlsx
	H	I	J
1	ES_ETA	ES_AVAIL_WHSE	NOTES
2	05-Dec-21	01-Jan-22
3	28-Dec-21	01-Feb-22
4	05-Nov-21	01-Dec-21
Sheet1 (3)

Cell Formulas
Range	Formula
I2:I4	I2	=EOMONTH([@[ES_ETA]],(DAY([@[ES_ETA]])>25)+0)+1

Named Ranges
Name	Refers To	Cells
'Sheet1 (3)'!ES_ETA	='Sheet1 (3)'!$H$2:$H$4	I2

 

[BigBeachBananas]

[REMOVED]

 

[BigBeachBananas]

Hi Excel wizards,
I have a date formula that is working in all ways except near year-end, when instead of rolling to the next year, it rolls back to the current year. I feel like I'm just missing one small part of the formula, but can't figure it out. Any help appreciated. Thank you!

Formula in cell I2 of the image is as follows: =DATE(YEAR([@[ES_ETA]]), MONTH(EOMONTH([@[ES_ETA]], (DAY([@[ES_ETA]])>25)+1)),1)

Try:

Book2
	A	B
1	12/5/2021	1/1/2022
2	12/28/2021	2/1/2022
3	11/5/2021	12/1/2021
Sheet1

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

 

[sheller]

Hi Sheller,

The YEAR is always that in ES_ETA but you add to the months so the year should change.

I'm not sure why your first two rows should be the 2nd of the month so can you explain or does this do what you ask?

Sheller4.xlsx
	H	I	J
1	ES_ETA	ES_AVAIL_WHSE	NOTES
2	05-Dec-21	01-Jan-22
3	28-Dec-21	01-Feb-22
4	05-Nov-21	01-Dec-21
Sheet1 (3)

Cell Formulas
Range	Formula
I2:I4	I2	=EOMONTH([@[ES_ETA]],(DAY([@[ES_ETA]])>25)+0)+1

Named Ranges
Name	Refers To	Cells
'Sheet1 (3)'!ES_ETA	='Sheet1 (3)'!$H$2:$H$4	I2

Thank you so much! This is exactly what I was looking for, and the 2nd of the month was a typo on my end (and should have been the first). Thanks again!

 

[Toadstool]

Thank you so much! This is exactly what I was looking for, and the 2nd of the month was a typo on my end (and should have been the first). Thanks again!

You're welcome and thanks for the feedback.