Fiscal Calendar Week

  • Thread starter Thread starter ak
  • Start date Start date
A

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
 
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
 
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.
 
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.
 
Back
Top