Formula for Trading year from date

[StuartR]

I have a spreadsheet with a date in column B on every row. I need to filter the data by "Trading year".

Year 1 runs from 1 Nov 2013 to 31 Oct 2014
Year 2 runs from 1 Nov 2014 to 31 Oct 2015
etc.

Can someone please suggest a simple formula so that I can add a "trading year" column that I can use to filter the data.

[Jan Karel Pieterse]

Something like:

Code: Select all

=YEAR(A1)-IF(MONTH(A1)<11,1,0)

[StuartR]

That's brilliantly simple. Thank you.

[HansV]

Alternatively:

=YEAR(EDATE(A1,-10))

[StuartR]

That works too Hans, but I found Jan Karel's formula easier to understand

[Rudi]

EDATE just calculates the amount of months before or after the given date...

1.jpg

[StuartR]

Rudi,

Yes, I looked at the help and realized how it works. Thank you.

[Rudi]

The hardest part of EDATE is to work out what the "E" stands for?
It would have been more appropriate to name it MDATE (for month)!!

[StuartR]

I ended up using EDATE as I needed to repeat it within the formula and it made everything a bit shorter. My final formula was
=IF(C3<>"",YEAR(EDATE(C3,-10)) & "-" & YEAR(EDATE(C3,2)),"")

Which produces
2013-2014
2014-2015
etc.
and leaves the cell blank on rows that haven't been completed yet

[HansV]

The hardest part of EDATE is to work out what the "E" stands for?
It would have been more appropriate to name it MDATE (for month)!!

I think it stands for "Effective Date", as in "the effective date is 2 months from now".

An eDate, on the other hand, is something entirely different...

[StuartR]

Thank you to everyone for your help, and the enlightening asides. I have now replaced lots of SUMIF() formulae with SUMIFS() and my financial spreadsheet now correctly allows me to select a financial year and displays just the data for that year.