8 Replies Latest reply on May 2, 2013 2:06 PM by brian.curran

    Calculating Holiday Entitlement, summing between two dates...

    brian.curran

      Hi,

      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?

       

      Thanks

      Brian.