Figuring out where you are in a repeating sequence

[uemtux]

Here is my problem:

I am working on an accounting system. Each record is equivalent to a paycheque. We have a field called PayPeriod which is basically a serial number. Each employee starts at 1 and for each pay the number is incremented by 1.

For an employee's first 20 pays, he is given 0.5 days of vacation for his bank. For the next 6 pays, 0 days in the bank. Then the next 20 pays after that it's back to 0.5 days added to the bank per pay and onwards like that theoretically forever.

My question is, based on the pay period number, can anyone come up with a formula that will tell me whether or not to add to the vacation bank on that day ?

Edited March 17, 2008 by Guest

[David Jondreau]

Does this work?

Case(Pay <20; 0; .5) + .5 * Div(pay;27)

[uemtux]

As it is, it doesn't work. You've got the 0 and 0.5 reversed. It kinda works in this version:

Case(PayPeriod ≤ 20; .5; 0) + .5 * Div(PayPeriod;27)

They get a half day on pay period 20 as well, so I replaced your less-than with a less-than-or-equal-to. Unfortunately, while it works with numbers 1-26, it continues returning 0.5 ever after.

[comment]

This is not very clear, but assuming you mean that the employee is given 0.5 days of vacation on pay periods 20, 46, 72, 98, 124, etc., try:

0.5 * not Mod ( PayPeriod - 20 ; 26 )

[uemtux]

I will try and be more clear.

Each record is a pay.

Each record has a serial number. For each employee, the first record is 1, the second record is 2, and so on.

For records 1-20, we add 0.5 days to the employee's vacation bank. (In other words, you earn 10 days of vacation over 20 pays)

For records 21-26 we add 0 days. (the next 6 pays don't earn you anything)

For records 27-46 we add 0.5 days (Then over the next 20 pays you earn another 10 days vacation)

For Records 47-52 we add 0 days. (Then another 6 pays with nothing added to the bank)

It cycles like this with 20 pays adding to bank and 6 pays not adding to bank. All the while the serial numbers are increasing.

I want a function that if you fed it any number between 1-20, or 27-46 (or any number futher in the sequence) would return a 0.5 or a 1 or any other consistent result so that I can know instantly whether or not to add vacation days to the bank based on the PayPeriod.

Edited March 17, 2008 by Guest

[David Jondreau]

Do you actually add .5 vacation days to a employee for every day they work? At 20 days an employee has 10 days of vacation? If not, how do you know if you've already added to their bank? If so, can I work for you?

Seriously, comment and I are thinking you want to calculate the Bank itself. But you're saying you just want to know if a number is between 1-20, 27-46, and so on. Don't you really want to know if a number is 1, 27, 53, and so on?

And wouldn't it be better for FM to calculate the number of vacation days earned?

[uemtux]

Do you actually add .5 vacation days to a employee for every day they work? At 20 days an employee has 10 days of vacation? If not, how do you know if you've already added to their bank? If so, can I work for you?

Not days, pays. 0.5 days added to bank for each of the first 20 paychecks. Then 6 paychecks where you get nothing. Then another 20 where you get 0.5 each, then another 6 pays of nothing, then another 20 pays at 0.5 days each, then another 6 pays of nothing, then another 20 pays at 0.5 days each... and so on.

Seriously, comment and I are thinking you want to calculate the Bank itself. But you're saying you just want to know if a number is between 1-20, 27-46, and so on. Don't you really want to know if a number is 1, 27, 53, and so on?

I kind of don't understand what you're saying here. No, I don't want to calculate the full bank. I merely want to be able to identify Paychecks that add to the bank vs. paychecks that do not add to the bank. There are other ways of doing what I need to do, but in the context of the existing solution, this is the way I have to do it. I cannot use a field that orders the records 1-26 or anything like that because in this system records can be deleted and re-created by the accountant/HR person at any time, and so my script, which calculates values for many fields including "Number of days to add to bank this pay" and "Total Vacation Bank Thus Far" has to contend with the fact that the only field which is known to be correct is the PayPeriod field. The field isn't even an automatic serial number. It's the responsibility of the accountant to enter the correct number when creating the record.

[comment]

It doesn't sound like a very robust system. In any case, I think the test you are looking for is:

Mod ( PayPeriod - 1 ; 26 ) < 20

[uemtux]

It wasn't engineered for robustness, it was created to suit its users who, while kind and decent people, would not be described as generally computer literate. They do things one way, and they aren't going to learn new procedures for anyone. This isn't how I would have designed it but it wasn't my call.

THANK YOU comment, that works perfectly, you just saved my bacon. I've really gotta work on understanding all of Mod's possible uses.