Formula to reduce just date by a year if month is jan feb or march

[Krang]

Hi all, Is there a formula please to deduct one year from a date if the month is january february or march. So if date is in format 30/03/2021 i wanted 2021 to show 2020, so just 1 year reduced from the year if the month is jan,feb or march.i didnt need the day or month to be reduced by a year, just the year itself then the day and date stay same. Thank you

 

[Joe4]

Welcome to the Board!

Try this formula, for a date in cell A1:

=IF(MONTH(A1)<4,EDATE(A1,-12),A1)

 

[JamesCanale]

MrExcelPlayground4.xlsx
	C	D
12	3/12/2021	3/12/2020
Sheet5

Cell Formulas
Range	Formula
D12	D12	=IF(MONTH(C12)<4,DATE(YEAR(C12)-1,MONTH(C12),DAY(C12)),C12)

 

[Krang]

Welcome to the Board!

Try this formula, for a date in cell A1:

=IF(MONTH(A1)<4,EDATE(A1,-12),A1)

Thank you thats really helpful and much appreciated

 

[Krang]

MrExcelPlayground4.xlsx
	C	D
12	3/12/2021	3/12/2020
Sheet5

Cell Formulas
Range	Formula
D12	D12	=IF(MONTH(C12)<4,DATE(YEAR(C12)-1,MONTH(C12),DAY(C12)),C12)

Hi thank you that is really helpful and much appreciated

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Krang]

You are welcome.
Glad we were able to help!

Thank you, i had another question please. Having reduced the year by one year as above if jan, feb or march are the months how could i only display the amended year and not the day or month please? So if date entered dd//mm//yy format the formula would as above reduce the year by one year if jan,feb or march month but would output just the year (either as minus a year or just the year as displayed as is if month is april- dec) thanks again for your help

 

[Krang]

Thank you, i had another question please. Having reduced the year by one year as above if jan, feb or march are the months how could i only display the amended year and not the day or month please? So if date entered dd//mm//yy format the formula would as above reduce the year by one year if jan,feb or march month but would output just the year (either as minus a year or just the year as displayed as is if month is april- dec) thanks again for your help

Sorry i mean if date entered dd/mm/yyyy i wanted to only show the year in format yyyy with the year reduced by a year if jan feb or march months or just the year in format yyyy without a years reduction if months are april - dec. Thank you

 

[Dave Patton]

You can custom format the result yyyy or for a Text result use Text.

T202109a.xlsm
	A	B	C	D
1	30-Mar-21	2020	2020	2020
2	1-Jan-21	2020	2020	2020
3
6c

Cell Formulas
Range	Formula
B1:B2	B1	=IF(MONTH(A1)<4,EDATE(A1,-12),A1)
C1:C2	C1	=IF(MONTH(A1)<4,DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)),A1)
D1:D2	D1	=TEXT(IF(MONTH(A1)<4,EDATE(A1,-12),A1),"YYYY")

 

[Krang]

Hi dave thats great thank you so much.