Program Year vs Calendar Year - Tracking the weeks

  • Thread starter Thread starter Simon Jester
  • Start date Start date
S

Simon Jester

I ran across a curious situation that someone who knew what they were
doing would probably be able to solve without even thinking about it.
I have to track some data about services delivered to our customers on
a weekly basis. I started off with a parameter query and asked for
the occurrence of each service between the date the week began and the
date the week ended. This was fine until I discovered that some
services were being entered and back-dated (perfectly legit but it
causes my numbers for previous weeks to be off). So I got this
brilliant idea to create a query that breaks down the services by each
week of the entire month. CalWeekNum: =Format([SERVICEDTE],"ww").
This way I won't have to run the query for each week looking for
numbers that might have changed. Works like a charm. Unfortunately,
everything I do is on a Program year - July to June. So I tried
another calculation: PYWeekNum:=Format([SERVICEDTE],"ww")-26. This
too works like a charm until you get to the 27th week and it starts
numbering the weeks backwards: 25, 26, -26,-25. Gets very confusing.
Does anyone know of a better way of getting the number of the PY week?
Any help is much appreciated.
Thanks much!
SJ
 
Back
Top