Calculating Amount of Hospital Leave (Ever Changing Number)

[Techno_Technoso]

Not sure if this the right sub-forum to ask (or if this belongs to base design), but I do have a problem.
Context is I am making a HR Leave Management System for my team and am trying to calculate Hospitalisation Leave. It is different from leaves like annual leaves where it is based on the years of service and that it resets every year.

The way this leave is calculated is if you are entitled 2 hospitalisation leave for every month you have worked in the first year, and 4 hospitalisation leave every month after the 12th month(from the 13th month onwards), with a cap of 120 days of hospitalisation leave. I can easily count how many days each employee currently are entitled with these 2 formulae in 2 different fields:

To calculate the amount of hospitalisation leave

IF({Number of months} <= 12, {Number of months}*2, IF({Number of months} > 12, (({Number of months}-12)*4)+24, 0))

120 days limit

IF({days of SL before limit} >= 120, 120, IF({days of SL before limit} < 120, {days of SL before limit}, 0))

Here comes the problem:
If someone were to actually take a hospitalisation leave of say 20 days, how do I ensure that after subtracting the 20 days off, the current leave I have will still be accumulated (+2/+4 every month) to the cap of 120?

[kuovonne]

Techno_Technoson:

If someone were to actually take a hospitalisation leave of say 20 days, how do I ensure that after subtracting the 20 days off, the current leave I have will still be accumulated (+2/+4 every month) to the cap of 120?

Does being on hospitalization leave affect the accumulation of hospitalization leave?

Can you just add two more fields: one for the total number of days of hospitalization leave taken, and another for the days available? The days available would be the days accrued (the last formula in your post) minus the days taken.

[Techno_Technoso]

Yes I do have a field for total number of days of hospitalisation which is a rollup field. I did try to minus off the days taken with my last formula which gives me the correct amount of days back. However, the remaining days does not increase not as I increase the months(+1 month should either +2 or +4 to the number of days). The number just stays the same

[TheTimeSavingCo]

Hmm, I think to do this we’d have to have a table that deals with leave on a per transaction basis and roll up on that

This would allow us to handle the following scenario:

(To make things easier to understand, let’s assume the entitlement is 1 day of leave per month, to a cap of 10 days)

1. Month 9: 9 days of leave
2. Month 10: 10 days of leave
3. Month 11: 10 days of leave
   • Person takes 3 days of leave here
   • New value: 7 days of leave
4. Month 12: 8 days of leave

The transaction table would be populated via an automation that would add a record per person, per month only if that person had not reached their cap yet

If it were me, I would handle this via an automation that ran once a month (on the day that leave gets accumulated), and would:

• Run a script that would look for all the people who had not yet reached their cap yet
• For each of these, add a record to the transactions table with the amount of leave they should get, including the logic of not exceeding their cap

If a script is out of the question, then I would handle this via an automation where the trigger was “When record enters view”, set the filter for said view appropriately, and create an Entitlement record in the transactions table appropriately

Here’s an alpha of what I’m talking about that you can use if you’d like

[Techno_Technoso]

Yea after hours of thinking through I might have to do it via automations. Not too familiar on how to code out a script so might not use that. Will have to give some thought on the work flow and process for the automation. Thanks!

[HERRY1O1]

When calculating the amount of hospital leave, especially with fluctuating numbers, it's crucial to account for variable factors such as recovery times and medical advice. For construction estimation, factoring in potential delays due to worker hospital leaves can provide a more accurate project timeline and budget, ensuring better preparedness and resource allocation.

[BlazeEstimating]

When determining hospital leave, especially with fluctuating figures, it is important to consider factors such as recovery periods and medical recommendations. In construction estimating services, incorporating potential delays due to worker hospital leaves helps create a more realistic project timeline and budget, leading to improved preparedness and resource management.