[Pling]

Here’s the problem…
My data set is a list of Employee IDs and salaries.
Each employee ID has a start and end date with a salary amount.
I need to assign a financial year to every row in my data set and also the appropriate dates (see example below).
There needs to be a row for every year.
Spanned years need to start on 01/01/yyyy and end 31/12/yyyy.
Financial year needs to be the year of the end date.

So this:
Start End Amount Financial year
01/01/2006 31/12/2008 1000

Needs to go to this:
01/01/2006 31/12/2006 1000 2006
01/01/2007 31/12/2007 1000 2007
01/01/2008 31/12/2008 1000 2008

The additional rows need inserting with appropriate dates. Additionally the span could go to 2 or 3 years, i.e. the salary could be the same for 2 or 3 years (like mine!) so would need to insert a row and appropriate dates for those years.

Any help would be greatly appreciated.
Cheers

[stuschmied]

Hi,

I've attached a small spreadsheet to demonstrate how you can do this. I was really bored at work this morning and it was kind of fun coding it. I put the creation of the new records on the on-click event of a button, but you can move that to anywhere you want. The date format is in MM/DD/YYYY so you'll have to change that for it to work right for your needs.

Good luck, let me know if this helps.

Stu