How do I get Excel to display which financial year a date is in?
 

Hi
I have a list of dates in an Excel 2000 spreadsheet. I'm looking for a
function that will display which financial year those dates fall in. So for
example if the date was 31/01/2009 I would want it to show 08-09.
Does anyone know of a function that would do this?
thanks

How do I get Excel to display which financial year a date is in?
 

Hi,

It depends when your financial year ends, are they all the same? Try this
for a year ending on 31 March. If ot's different that End March change <4 to
the month you want

=IF(MONTH(A1)<4,YEAR(A1)-1&"/"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"LemLems" wrote:

Hi
I have a list of dates in an Excel 2000 spreadsheet. I'm looking for a
function that will display which financial year those dates fall in. So for
example if the date was 31/01/2009 I would want it to show 08-09.
Does anyone know of a function that would do this?
thanks

How do I get Excel to display which financial year a date is i
 

Ah,

A formatting issue you wanted 08-09

=IF(MONTH(A1)<4,RIGHT(YEAR(A1)-1,2)&"-"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"Mike H" wrote:

Hi,

It depends when your financial year ends, are they all the same? Try this
for a year ending on 31 March. If ot's different that End March change <4 to
the month you want

=IF(MONTH(A1)<4,YEAR(A1)-1&"/"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"LemLems" wrote:

Hi
I have a list of dates in an Excel 2000 spreadsheet. I'm looking for a
function that will display which financial year those dates fall in. So for
example if the date was 31/01/2009 I would want it to show 08-09.
Does anyone know of a function that would do this?
thanks

How do I get Excel to display which financial year a date is i
 

I'll get the formatting right in a minute:(

=IF(MONTH(A1)<4,RIGHT(YEAR(A1)-1,2)&"-"&RIGHT(YEAR(A1),2),RIGHT(YEAR(A1),2)&"-"&RIGHT(YEAR(A1)+1,2))

Mike

"Mike H" wrote:

Ah,

A formatting issue you wanted 08-09

=IF(MONTH(A1)<4,RIGHT(YEAR(A1)-1,2)&"-"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"Mike H" wrote:

Hi,

It depends when your financial year ends, are they all the same? Try this
for a year ending on 31 March. If ot's different that End March change <4 to
the month you want

=IF(MONTH(A1)<4,YEAR(A1)-1&"/"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"LemLems" wrote:

Hi
I have a list of dates in an Excel 2000 spreadsheet. I'm looking for a
function that will display which financial year those dates fall in. So for
example if the date was 31/01/2009 I would want it to show 08-09.
Does anyone know of a function that would do this?
thanks

How do I get Excel to display which financial year a date is i
 

Genius! Thanks Mike, that's saved me a very frustrating morning!
Cheers
LemLems

"Mike H" wrote:

Ah,

A formatting issue you wanted 08-09

=IF(MONTH(A1)<4,RIGHT(YEAR(A1)-1,2)&"-"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"Mike H" wrote:

Hi,

It depends when your financial year ends, are they all the same? Try this
for a year ending on 31 March. If ot's different that End March change <4 to
the month you want

=IF(MONTH(A1)<4,YEAR(A1)-1&"/"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"LemLems" wrote:

Hi
I have a list of dates in an Excel 2000 spreadsheet. I'm looking for a
function that will display which financial year those dates fall in. So for
example if the date was 31/01/2009 I would want it to show 08-09.
Does anyone know of a function that would do this?
thanks

How do I get Excel to display which financial year a date is i
 

I was frustrating myself getting the formatting wrong. Glad I could help.

"LemLems" wrote:

Genius! Thanks Mike, that's saved me a very frustrating morning!
Cheers
LemLems

"Mike H" wrote:

Ah,

A formatting issue you wanted 08-09

=IF(MONTH(A1)<4,RIGHT(YEAR(A1)-1,2)&"-"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"Mike H" wrote:

Hi,

It depends when your financial year ends, are they all the same? Try this
for a year ending on 31 March. If ot's different that End March change <4 to
the month you want

=IF(MONTH(A1)<4,YEAR(A1)-1&"/"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

Mike

"LemLems" wrote:

Hi
I have a list of dates in an Excel 2000 spreadsheet. I'm looking for a
function that will display which financial year those dates fall in. So for
example if the date was 31/01/2009 I would want it to show 08-09.
Does anyone know of a function that would do this?
thanks