payroll weeks / varying first day and week of the year

[Claire]

I have a somewhat ugly situation. Our year/first day/first week of the year
changes depending on which day of the week January 1st is. My goal is
twofold:
1. To be able to find the first day of the yearly payroll for any given
date (and the corresponding end of that year)
2. To be able to find the payroll week number for any date (ie 12/14/08 is
week 51).

The first week of the year begins on second Monday before the first Friday
of the new year. IE 2009 starts on Monday December 22, because the first
2009 Friday is January 2 (the Monday before that is Dec 29, and the second
Monday before it is Dec 22).

I have translated this to mean that if the weekday (with Monday as the first
day of the week) of January 1st is <6 (ie Mon-Fri), then the first day will
be the Monday the week before January 1st. If the weekday is >=6 (Sat,Sun),
the first day of the payroll year is the Monday of that week.

I figure if I can figure out how to calculate the first day of the year,
then the last day of that year and the week number of the year should be more
manageable. I have pasted my first attempt before. The arguments are not
correct at this time (which doesn't surprise me given how nested it is) but I
haven't dealt with that yet, as I'm hoping someone out there has an idea of
how to simplify this mess.

Thank for any help, and here's the crazy first attempt:
iif(weekday(dateserial(datepart("yyyy",#12/14/08#),1,1),2)<6,
dateadd("ww",-1,(dateserial(datepart("yyyy",#12/14/08#),1,1)
-weekday(dateserial(datepart("yyyy",#12/14/08#),1,1),2)+1),
(dateserial(datepart("yyyy",#12/14/08#),1,1)
-weekday(dateserial(datepart("yyyy",#12/14/08#),1,1),2)+1)))

 

[Phil Smith]

It is -12 days from Friday until second monday before. Right?
weekday of Friday=6

What Day of the week is Jan 1?
Weekday(#1/1/2009)=5
The difference between that and Friday is 1. 1-12 = -11. Jan 1st -11
is December 22.

so (6-weekday(#1/1/2009#) is # of days from the first till friday.Since
Jan1 falling on a Saturday would screw this up, we add seven to this IF
Jan1st is a saturday.

(6-weekday(#1/1/2009#)+iif(weekday(#1/1/2009#)=7,7,0))
(The iif statement takes care of the possibility that 1/1/09 starts on a
saturday.

# of Days until First day of your payroll year?


Dateadd("d",+(6-weekday(#1/1/2009#)+iif(weekday(#1/1/2009#)=7,7,0))-12)
It counts up to Friday, then down until the second Monday.

My math might be off, cuz I have a headache and do not want to double
check, but the logic is sound. I may be off by one somewhere.









The difference between that Friday and the monday you want is always
going to be 12 days.

So you want a formula that adds (6-Weekday(#1/1/2009

Dateadd("d",-(6-Weekday(#1/1/2009)-12)

 

[Phil Smith]

Sorry. I had to test it. The correct formual is:

DateAdd("d",(6-Weekday(#1/1/2009#)+IIf(Weekday(#1/1/2009#)=7,7,0)-11),#1/1/2009#)

I thought I was off by one.