Formula - Value if True not returning valid year

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Hi there,

I have a formula which the "Value if True" result is not calculating correctly.

For example, if the date in cell D7 is 30/06/18 return 2016-17 but if the month of cell D7 is December, return the year of D7 minus 1 year.

The bit that doesn't seem to be working is YEAR(D7)-1
This returns 1905 instead of 2017.

=IF(MONTH(D7)=12,YEAR(D7)-1,TEXT(DATE(YEAR($D$7)-2,MONTH($D$7),DAY($D$7)),"yyyy")&TEXT(DATE(YEAR($D$7)-1,MONTH($D$7),DAY($D$7)),"-yy"))

Can someone see where I am going wrong please?

Thank you!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Watch MrExcel Video

Forum statistics

Threads
1,102,305
Messages
5,486,072
Members
407,531
Latest member
WalterR01

This Week's Hot Topics

Top