09-15-08, 07:56 #1Registered User
- Join Date
- Sep 2008
Unanswered: Inserting financial years & dates
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.
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.
09-16-08, 11:57 #2Registered User
- Join Date
- May 2008
- Raleigh, NC
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--If its free, take it for what its worth!