Show Annual Year

[ps65]

Please see the attached spreadsheet.

I am trying to get excel to workout the annual year based on the cashflow end date.

As you will see, row 6 is currently manually entered. I would like it to increase for each year that increases: so,

2013 = 1
2014 = 2
2015 = 3

Some people mentioned using a MOD function, but I have had no luck?

Any help would, as ever, be greatly appreciated!

[Andrew-R]

In cell D6 try:

=MOD(YEAR(D3),YEAR(CFSTART))

And copy across.

[martindwilson]

=max(0,YEAR(D4)-2012) would probably do it

[ps65]

Thank you, sorry can I ask one more question.

What is I wanted it so it was the cashflow year?

D6 = 0

E6 to P6 = 1

and so on and so on...

[ps65]

Thank you, sorry can I ask one more question.

What is I wanted it so it was the cashflow year?

D6 = 0

E6 to P6 = 1

and so on and so on...

[Andrew-R]

So what date does your cashflow year start on?

[ps65]

Sorry, there was a mistake in my spreadsheet.

Assume CFSTART is any date, say 13/02/2013 for argument sake, that is considered day 0.

14/02/2013 is therefore today and considered Year 1, until 14/02/2014 (year 2)

[Andrew-R]

OK, how about something like:

=DATEDIF(D3,CFSTART,"y")+1

[ps65]

Perfect - I'm liking your ideas! Thanks so much for the answer and quick response!