| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| year quarters === Financial Year | Excel Discussion (Misc queries) | |||
| How to convert date to financial year format 2006-07 | Excel Worksheet Functions | |||
| How to compare a date with financial year | Excel Discussion (Misc queries) | |||
| Display date as 06/08 (m,yy) without year changing back to 07? | Excel Discussion (Misc queries) | |||
| How do I get Excel to automatically calculate salaries actually received in financial year? | Excel Discussion (Misc queries) | |||
Linear Mode
