I'm working on an Employees database and want to calculate holiday entitlement.
In the "Employees" table, I specify the holiday year (01 May to 30 Apr) and the number of days holiday (28)
In the "Absence" table, which will appear as a portal on the Employees layout, I want to record the start and end dates of a holiday and have the fields below calculated:
_cElapsedDays Holiday start date (31 Aug) - Holiday Year start date (01 May) = 122 days elapsed
_cElapsedMonths _cElapsedDays / 30.33 = 4 months elapsed
Days Accrued 28 days / 12 months X _cElapsedMonths = 9.33 days accrued
Balance Days Accrued - Taken - Requested = 1.33
The calculation for the "Taken" field is where I'm struggling, I want to add up all previously "Requested" days but only from the start of the Holiday year up until the current Start date. Does that make sense?