A little question about an Excel formula

[Peter B]

I like to think I'm okay at the simpler tasks of exploiting Excel, but I have this little problem for which I'm hoping to outsource the solution.

My current job involves doing leave audits. Not terribly exciting but it pays the bills. The audit is recorded on an Excel file which was created several years ago by my current supervisor. There's a little problem with the file which she never solved. It's not a major problem and it's easily fixed for each audit, but it strikes me that there should be a really simple solution.

The issue is long service leave. Here in Australia, Commonwealth employees are entitled to 90 calendar days of long service leave after 10 years of service, and an extra 9 days per year after that.

The file involves you specifying the date the employee started with the agency. The file then adds 10 years to the start date, which thus provides the date the employee is eligible for their first 90 days of long service leave. Then, in the cell below the 10th anniversary date, we want the file to show the date one year later, and so on down the file, up to the last anniversary date before today.

So, for example, an employee starting on 21 December 2002 would have their 10th anniversary on 21 December 2012, and then anniversaries on 21 December 2013 and 2014 (and would now have a total of 108 days of leave, minus any leave taken).

The solution used by my supervisor to calculate these anniversary dates was to add 365 days to the previous anniversary (that is, 21/12/2012 + 365 days = 21/12/2013). But the problem (as she herself recognised) is those pesky leap years. Thus, every four years 366 days need to be added instead.

Presumably the solution is to replace "+365" with some sort of "If-Then" formula. But I'm unsure how to write it. Does anyone have any suggestions?

Thank you.

[Andromeda]

Why can't you just add one year, in the same way that you earlier added 10 years to the start date?

[Glom]

Deconstruct the date, modify, and reconstitute.

The YEAR formula extracts the year of the date as you'd imagine. DATE allows you to build a date from year, month and day of the month.

So DATE(YEAR(yourdate)+1,MONTH(yourdate),DAY(yourdate)) increments yourdate by a year.

Is Commonwealth employee like a federal employee?

[Luther]

Deconstruct the date, modify, and reconstitute.

The YEAR formula extracts the year of the date as you'd imagine. DATE allows you to build a date from year, month and day of the month.

So DATE(YEAR(yourdate)+1,MONTH(yourdate),DAY(yourdate)) increments yourdate by a year.

Is Commonwealth employee like a federal employee?

This will work.  If the original date is February 29th, the output will be March 1st the next year.  If you want to map it to February 28th in this case instead, then try something like

DATE(YEAR(yourdate)+1,MONTH(yourdate),DAY(yourdate)-IF(MONTH(yourdate)=2,IF(DAY(yourdate)=29,1,0),0))

[ka9q]

If the definition of a "year" is the same month and day with the year incremented, I guess that's as good as any though you'd still have to handle leaplings as a special case. (A leapling would be somebody whose anniversary date is February 29).

Another way is to do all your computations in days, with a year defined as 365.2425 days, and perform integer truncation or rounding (your choice). This would cause a person's yearly anniversary to move around a little from year to year, just as the exact start of spring does, but it's arguably more faithful to the definition of a "year". Otherwise you're being slightly unfair to those whose 10 years of service happen to span more February 29ths than others.

[Peter B]

Why can't you just add one year, in the same way that you earlier added 10 years to the start date?

Because at the moment the 10th anniversary is calculated by adding 3652 days to the start date. And as it's possible for an employee to have three leap years in 10 years of service, that 3652 day figure isn't always accurate either. So I suppose I'd need to use the new formula to calculate the 10th anniversary too.

[Peter B]

Deconstruct the date, modify, and reconstitute.

The YEAR formula extracts the year of the date as you'd imagine. DATE allows you to build a date from year, month and day of the month.

So DATE(YEAR(yourdate)+1,MONTH(yourdate),DAY(yourdate)) increments yourdate by a year.

Excellent. Thank you very much.

Is Commonwealth employee like a federal employee?

Yes. That is, a person employed by the Commonwealth of Australia under one of a number of Acts of Parliament. Most public servants (including me at the moment) are employed under the Public Service Act 1999, but quite a few government agencies employ their staff under acts specific to that agency, like the Australian Federal Police Act 1979.

[Peter B]

If the definition of a "year" is the same month and day with the year incremented, I guess that's as good as any...

Yes, that's the definition we use.

...though you'd still have to handle leaplings as a special case. (A leapling would be somebody whose anniversary date is February 29).

:-) As my calculations suggest the odds of a person being born on 29 February are 1 in 1461, I think I can handle occurrences of that frequency.

Another way is to do all your computations in days, with a year defined as 365.2425 days, and perform integer truncation or rounding (your choice). This would cause a person's yearly anniversary to move around a little from year to year, just as the exact start of spring does, but it's arguably more faithful to the definition of a "year". Otherwise you're being slightly unfair to those whose 10 years of service happen to span more February 29ths than others.

No, our employment rules work as described above. Although...

...there are annoying exceptions to the long service leave rules. For example, with this leave type a month is defined as 30 days, but also as the period from a given day in a month to the previous day of the next month. So a person who takes a complete calendar month on long service leave always has 30 days deducted from their balance, regardless of the length of the month. Not surprisingly we advise people not to take long service leave over the end of February.

[Luther]

If the definition of a "year" is the same month and day with the year incremented, I guess that's as good as any though you'd still have to handle leaplings as a special case. (A leapling would be somebody whose anniversary date is February 29).

Per my earlier post, the proposed procedure would roll over the later date onto March 1st, if no February 29th exists in that year.

If the desired behaviour is to move back to February 28th for years with no February 29th, then I posted a method for that, which will work if the number of years being incremented guarantees that, if the original year has February 29th, then the incremented year doesn't (one year and ten years would both work).  If the number of years being incremented is an integer multiple of four, and we don't have to worry about dates as far back as 1900 or as far forward as 2100, then there is no need to worry about it.  If the number of years being incremented is unknown (let's say, it's a number in another cell), so that when the source year has a February 29th, the target year may or may not, then something more sophisticated than my formula is needed.  But it's certainly possible to do it.