Date calculation

[SteveS]

Last year I designed an employee attendence record database. I have a calculation field called (days available). The calculation adds 1.25*month(today). After each month, 1.25 days are automatically added to the (days available) field for each employee. This worked fine until 2006. All of the days reverted back to 1/1/2005 and deducted 15 days from each employees (days available).

Can anyone tell me how to include the year for this calculation?

Your help would be greatly appreciated.

Steve

[Mike_H]

Hi Steve,

Throwing an idea out here.... If you check in the define fields, is that field limited to a date range? And if so, try changing it for the current year under the Validation/ Date Range option.

i.e. I have a few of our entry fields limited to a date range to avoid data entry errors. The drawback is that I have to change that date range each time I want to shift the entry period.

[Lee Smith]

I have a similar questions, so I'm subscribing to this topic.

Too bad we can't do this without creating a reply.

Lee

[comment]

Perhaps one of you could clarify the question, because I sure don't get it. The formula

1.25 * Month( Today)

looks ONLY at the current month and multiplies it by 1.25. The result will be 1.25 for every day in January - no matter what the year.

If you want to add 1.25 days per month, you ought to have a starting date somewhere, so that you can tell how many months have elapsed since then until today.

[Lee Smith]

Hi comment,

It isn't the calculation of 1.25 (month) that I was interested in following, I just want to see what others come up with as a solution.

Lee

:(

[SteveS]

Mike_H

I am sorry for the delayed response, I tried changing the Validation / Date Range option which did not work.

Thank You

Steve

[SteveS]

Comment,

I would like to apologize to you too for my delayed response, after 12 days, I did not think anyone would respond to my question.

I believe you are right when you said I should have a starting date somewhere. How can I do this?(tell how many months have elapsed since then until today)?

Steve

[comment]

Gee, I don't know - I suppose it could be in a global field, but could you tell us more about what your solution is supposed to do? And when is "then"?

[SteveS]

The solution involves employee's sick time records. At the end of each month, all employees receive an additional 1.25 days added to their accumulated (earned days) total. The involved fields are the following: "Year" = (date field), "Earned Days" = (number field). I want the year to start on 1/1/2006. The script or calculation should add 1.25 days to the employees (earned days) total after each month and continue to do this into 2007, 2008 & beyond.

[comment]

I am sorry, but I still don't understand this fully. Let's take the easy part first:

"At the end of each month, all employees receive an additional 1.25 days added to their accumulated (earned days) total."

For this, we need to know the employee's hire date. After all, if someone was hired in July, at the end of the year they would have half the sick leave of someone hired in January, no?

Now, to compute this, you could do:

( ( Year ( Status(CurrentDate) ) - Year ( HireDate ) ) * 12 + Month ( Status(CurrentDate) ) - Month ( HireDate ) ) * 1.25

Here the employee gets the full 1.25 days for the first month, even if HireDate falls later in the month.

This will always show the total accumulated days - since date of hire.

The hard part for me to figure is the significance of the date 1/1/2006. I would understand if you'd said that at the end of the year you need to pay the employees for their acumulated days, and start anew. This is not simple, but certainly doable. But you say you want to carry the earned days "into 2007, 2008 & beyond", so that leaves me puzzled.

[Lee Smith]

Hi comment,

Here the employee gets the full 1.25 days for the first month, even if HireDate falls later in the month.

This will always show the total accumulated days - since date of hire.

Yep.

The hard part for me to figure is the significance of the date 1/1/2006. I would understand if you'd said that at the end of the year you need to pay the employees for their acumulated days, and start anew. This is not simple, but certainly doable. But you say you want to carry the earned days "into 2007, 2008 & beyond", so that leaves me puzzled.

This is the hard part.

You need an effective date for the benefit accrual

You need to post this to an account, so that if there is a change to these, then the new rate doesn't affect the previous balances.

I don't see a way to do this part with calculations, it seems like you would need a script to post the accumulated totals balance field. This seems like an annual event, plus when ever the benefit changes.

There could also be a maximum number of days that can be carried over to consider.

Lee

Edited January 26, 2006 by Guest

[SteveS]

To Comment & Lee Smith,

Thank you for your assistance in advance.

I am sorry I have not made myself clearer.

I used the year 1/1/2006 as an example for the (current year) starting date. The employees who had previous accrued time(days), I entered the correct number inside the (earned days number field) which was included in the calculation.

I believe Comment was correct with the calculation given, I tested it and it worked! ((Year(Status(CurrentDate))-Year(HireDate)) * 12+ Month (Status(CurrentDate))-Month(HireDate))*1.25.

Thank you so much for your help, I never would have figured this out without your help.