Fiscal Periods

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a simple formula to change a month (DATE) number to a fisca
period? I can do this with formulas and IF statements or, looku
tables but these are clumsy

My July is period 1
My January is period 7

Any help is of course appreciated

Warman
 
Warman,

here is a very simple approach

=CHOOSE(MONTH(A1),7,8,9,10,11,12,1,2,3,4,5,6)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A couple:

=MONTH(EOMONTH(A1,6))

or

=MOD(MONTH(A1)+5,12)+1

EOMONTH is a function in the Analysis Toolpak Add-in (Tools/Add-ins...)

OTOH, a UDF isn't difficult:

Public Function FP(dDate As Variant) As Variant
Dim n1904 As Boolean
If TypeName(Application.Caller) = "Range" Then _
n1904 = Application.Caller.Parent.Parent.Date1904
FP = Month(DateAdd("m", 6, CDate(dDate) - n1904))
End Function


Call as

=FP("7/12/2004") ===> 1

or

A1: 4 Apr 2004
A2: =FP(A1) ===> 10
 
Back
Top