Create calendar table that defines pay schedule

[#1]

Is there a way to create a calendar table that defines the pay schedule?

We get paid every 2 weeks, but the specific pay date is not recorded in our system.

So say today is the day everyone gets their check. Today's Check (02/17/2012) includes the dates 01/30/2012 - 02/12/2012

I want to create a master calendar that I can tie into ours time ticket table that will allow me to group the correct dates into each pay schedule.

Is something like this possible?

[Anonymous]

Here as a possible solution.

First, take a known payday, and store it in a variable, for example:

LET D = makedate(2012,2,17);

Next, in your master calendar, add a flag, so PayDay=1 means it is a payday

if(mod(Date-'$(D)',14)=0,1,0) as PayDay

[#3]

This would be close, but would it be possible to list the date for the scheduled pay with each date?

Say instead of having a flag just for the 17th,  the dates from 01/30/2012-02/12/2012 would have a corresponding field that has the value 02/17/2012?

[Anonymous]

Sure, it is the same:

if(mod(Date-'$(D)',14)=0,Date) as PayDay

[#5]

I tried this expression and it does not return anything, just - for missing or null

[Anonymous]

What could be wrong:
- Check your variable - what it shows if you put it in a text box?
- Make sure your Date field is the integer date, not a timestamp.  If you're not sure, add floor() function:
if(mod(floor(Date)-'$(D)',14)=0,Date)

[#7]

When the variable is placed in a text box it returns this: 6.2189054726368e-005 which when the Date function is applied returns this: 12/30/1899

Edit:

Nevermind I forgot to change the variable to the one I used in the expression.

Still this is doing the same thing as the flag. On the actual pay day it displays the date. So say for the date 02/17/2012 it displays 02/17/2012.

I need it to display 02/17/2012 for all the dates between 01/30/2012 and 02/12/2012. 02/17/2012 should display the pay date 03/02/2012.

[CELAMBARASAN]

Hi,

     How you stored the 01/30/2012 - 02/12/2012 in table?

     I mean whole thing in single field or stored as from and to?

Celambarasan

[#9]

It is a single field that is recorded for the time ticket date. There is no reference on the record for when the actual pay date is.

[CELAMBARASAN]

Hi,

      Can you attach a sample data?

Celambarasan