Week Ending function (Pay Period)

[Rich Hartnett]

I had an application last week where I needed to calculate the date of the first day of the week that an event occurred it. That expression DATE - DAY_NUMBER(CDOW(DATE)) (provided by GGhandi) works fine.

But now I have an odd request, and I'm not sure how to modify the above expression (if at all possible) to get this calculation to work.

In this instance, I need to calculate the date of the ending of a pay period that ends on Thursday each week. (In other words, the start of the pay week is every Friday, and ends on the following Thursday.)

So for example, in this month of June 2016, whether an employee works on Friday 06/26/16 or Tuesday 06/28/16, it would still fall within the pay week which ends on Thursday 06/30/16.

Any thoughts as to how I could accomplish this?

Thanks,
Rich

[GGandhi]

Re: Week Ending function (Pay Period)

dim pay_week as d
dim pay_day as d
pay_week = pay_day+(5-day_number(cdow(pay_day)))

dim dd as d

dd = {06/26/2016}+(5-day_number(cdow({06/26/2016})))
?dd
= {06/30/2016}

[Rich Hartnett]

Re: Week Ending function (Pay Period)

Thanks for the quick response G.

I'm a little lost on the "Pay_Date" though. Is that the day the employee worked? If not, where does that date come from?

[GGandhi]

Re: Week Ending function (Pay Period)

pay_date or the day_employee_worked
i added the example to my code above take a look

dim dd as d

dd = {06/26/2016}+(5-day_number(cdow({06/26/2016})))
?dd
= {06/30/2016}

dim date_employee_worked as d
dim pay_week_of as d
pay_week_of = date_employee_worked +(5-day_number(cdow(date_employee_worked)))

to make it clear

edit:
while testing I do find some inconstancy,
I will repost the calculation in a minute

[GGandhi]

Re: Week Ending function (Pay Period)

the above did not work when the employee worked on Friday or Saturday, to correct I changed to a function

FUNCTION pay_week AS D (date_worked AS D )
	dim d1 as n
	dim d2 as n = 5
	d1 = day_number(cdow(date_worked))
	if d1 > 5 then 
		d2 = 12
	end if
	pay_week = date_worked + (d2 - d1)
END FUNCTION

if you want one line code

dim date_worked as d
dim week_of as d
week_of = date_worked + (if(day_number(cdow(date_worked)) > 5, 12-day_number(cdow(date_worked)),5-day_number(cdow(date_worked))))

[Rich Hartnett]

Re: Week Ending function (Pay Period)

That seems to work perfect!

As always, thank you Gandhi.