Calculating Holiday Entitlement, summing between two dates...

Discussion created by brian.curran on May 2, 2013
Latest reply on May 2, 2013 by brian.curran


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

Taken 2

Requested 6

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?