Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
If I have a financial year August01-July31, is there a worksheet function(s)
which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
If you have a date in A1: ="Qtr"&INT((IF(MONTH(A1)<8,MONTH(A1)+5,MONTH(A1 )-7)-1)/3)+1&" " & IF(MONTH(A1)<8,YEAR(A1)-1,YEAR(A1)) "Kurt" wrote: If I have a financial year August01-July31, is there a worksheet function(s) which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
With your date in cell A1; try the below formula
="Qtr"&LOOKUP(--TEXT(A1,"mm"),{1,2,5,8,11},{2,3,4,1,2}) & TEXT(A1," yyyy") If this post helps click Yes --------------- Jacob Skaria "Kurt" wrote: If I have a financial year August01-July31, is there a worksheet function(s) which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
LOOKUP(--TEXT(A1,"mm"),{1,2,5,8,11},{2,3,4,1,2}) is a much better way of doing it than my way, but TEXT(A1,"yyyy") won't work as 23/03/10 is in financial year 2009, so you'll need the if(month(A1)...) still. "Jacob Skaria" wrote: With your date in cell A1; try the below formula ="Qtr"&LOOKUP(--TEXT(A1,"mm"),{1,2,5,8,11},{2,3,4,1,2}) & TEXT(A1," yyyy") If this post helps click Yes --------------- Jacob Skaria "Kurt" wrote: If I have a financial year August01-July31, is there a worksheet function(s) which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
Hi,
Type this in range C9:C12 - 2,5,8,11. Type this in range D9:D12 - Qtr 3,Qtr 4,Qtr 1,Qtr 2. Assuming your date in cell B15, in cell C15, you may use the following formula =VLOOKUP(MONTH(B15),$C$9:$D$12,2)&", "&IF(MONTH(B15)<8,YEAR(B15)-1,YEAR(B15)) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Kurt" wrote in message ... If I have a financial year August01-July31, is there a worksheet function(s) which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
Yes you are right. I havent noticed that..
="Qtr "&LOOKUP(--TEXT(A1,"m"),{1,2,5,8,11},{2,3,4,1,2}) & " " & YEAR(A1)-(--TEXT(A1,"m")<8) If this post helps click Yes --------------- Jacob Skaria "Sam Wilson" wrote: LOOKUP(--TEXT(A1,"mm"),{1,2,5,8,11},{2,3,4,1,2}) is a much better way of doing it than my way, but TEXT(A1,"yyyy") won't work as 23/03/10 is in financial year 2009, so you'll need the if(month(A1)...) still. "Jacob Skaria" wrote: With your date in cell A1; try the below formula ="Qtr"&LOOKUP(--TEXT(A1,"mm"),{1,2,5,8,11},{2,3,4,1,2}) & TEXT(A1," yyyy") If this post helps click Yes --------------- Jacob Skaria "Kurt" wrote: If I have a financial year August01-July31, is there a worksheet function(s) which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
="Qtr"&MAX(1,INT((MOD(MONTH(A1)+4,12)+3)/3))&","&YEAR(A1)-(MONTH(A1)<8)
-- __________________________________ HTH Bob "Kurt" wrote in message ... If I have a financial year August01-July31, is there a worksheet function(s) which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
Roughly the same idea, but implemented in a slightly different way (one function call less)...
="Qtr "&(1+MOD(INT((MONTH(A4)+4)/3),4))&", "&(YEAR(A4)-(MONTH(A4)<8)) Just as an alert to the OP, your formula omitted a couple of "neatening" spaces (which can easily be added, of course) that the OP's examples showed he apparently wanted. -- Rick (MVP - Excel) "Bob Phillips" wrote in message ... ="Qtr"&MAX(1,INT((MOD(MONTH(A1)+4,12)+3)/3))&","&YEAR(A1)-(MONTH(A1)<8) -- __________________________________ HTH Bob "Kurt" wrote in message ... If I have a financial year August01-July31, is there a worksheet function(s) which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates in a Financial Year
I use this formula to show the fiscal year and quarter:
="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) So if the fiscal year starts on Oct 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<10)&"-Q"&INT(1+MOD(MONTH(A1)-10,12)/3) It results in an expression like: FY2009-Q1 It makes sorting easier. Kurt wrote: If I have a financial year August01-July31, is there a worksheet function(s) which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or 23/03/10 to read 'Qtr 3, 2009' etc. Any advice? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Financial Year Weeknum | Excel Discussion (Misc queries) | |||
Financial Year Month Number | Excel Worksheet Functions | |||
Pivot Table by Financial Year | Excel Discussion (Misc queries) | |||
year quarters === Financial Year | Excel Discussion (Misc queries) | |||
How to compare a date with financial year | Excel Discussion (Misc queries) |