Fiscal Calendar Week

[ak]

Column A: Site ID
Column B: Data dates (07/29/03 format)
Column C: Need to have dates in column B changed to
match a fiscal calendar by week

Details:
Fiscal calendar for 2003 begins 12/30/02 and ends 12/28/03
12/30/02 is month 1 week 1 (showing up as "1+1")todays'
date 07/29/30 would be month 7 week 5 ("7+5")
12/28/03 is month 12 week 5

So, a 08/01/03 date would bring back 7+5

Thanks
AK

 

[Dick Kusleika]

AK

July has only four Mondays, so how does it get 5 fiscal weeks?

 

[ak]

July's fiscal week (27) begins 06/30/03 and August's first
week begins 08/04/03.

Hope you can help with this formula.

Thanks
ak

 

[Daniel.M]

Hi,

Why?
What's your criteria that makes 1st July week start in June and 1st August
week start in August.

Weeks are having 7 days and months are having number of days not easily
divided by 7 (without remainder). So, some days of Month A will be counted
in Month A+1. You give the rule of when that should occur and people can act
upon it.

I'm asking the same question as Dick.

Regards,

Daniel M.

 

[Dick Kusleika]

AK

It sounds like you will need a lookup table with all of the months starting
dates. I can't tell how to figure out when the first week of July starts
without you telling me. That makes me think that someone just sets those
dates at the start of the year and that there is no formula to figure it
out. Correct me if I'm wrong on that point.

If I'm right, then you could have a table like this in F1:G12

12/30/02 1
2/3/03 2
3/3/03 3
3/31/03 4
5/5/03 5
6/2/03 6
6/30/03 7
8/4/03 8
9/1/03 9
9/29/03 10
11/3/03 11
12/1/03 12

the first column shows the Monday of week1 for the month in the second
column. Then in A1, you would have the date and in B1, this formula

=VLOOKUP(A1,F1:G12,2)&" -
"&(((A1+IF(WEEKDAY(A1)=1,-5,2)-WEEKDAY(A1))-VLOOKUP(A1,F1:F12,1))/7)+1

Give that a try and let me know if it needs changing.