I know the following.
Column I,J,K,M,N
Fiscal Yr
Fiscal Wk
Fiscal Qtr Num
Fiscal Qtr
Completed Date
What I am trying to determine is what week in the fiscal QTR are we at based on the Completed Date?
Our Fiscal Year Starts in February and our weeks start on Saturday and end on Friday.
This year we started our Fiscal Year on Jan 30 2016.
We use 13 weeks as a default within each Fiscal QTR.
I tried this formula in another worksheet just using the completed date and it does not match up.
=1+(WEEKNUM(EDATE(B2,-1)))-(WEEKNUM(DATE(YEAR(EDATE(B2,-1)),LOOKUP(MONTH(EDATE(B2,-1)),{1,4,7,10}),1)))
Results
The 30th of Jan to the 5 of Feb should be week 1 of Q1.
(Fiscal Week in QTR) (Completed)
14--------------------1/31/2016
1---------------------2/01/2016
2---------------------2/05/2016
2---------------------2/07/2016
3---------------------2/10/2016
any help would be appreciated.
Dal.
Bookmarks