Why not start with an Excel file pre-populated with the date of every pay day on its own row.
This is kind of what I wanted to do from the beginning. The problem was getting it to work so that:
1. I needed to be able to do a successful "COPY DOWN" operation on the dates in such a way so that they would continue to work into any date in the future. This proved to be surprisingly tricky. If I simply pre-populated a few dates, that was all well and good, but then when I did a copy-down, it would extrapolate the number of DAYS between each date, rather than extrapolating that each date was a MONTH apart. That meant that each date slowly crept off of the 15th-&-Last day of each month. That was useless because I didn't want to type out (or hand-correct) every single date for 10 years in the future.
2. Everything needed to survive the insertion of new rows in the middle of the spreadsheet.
In the end I did an interesting layout that worked as follows. The formulas looked like this:
A B C D E
--------------------------------------------------------------------------------------------------
1 |
2 | Month Date Description Type Hrs
3 |
4 | 01/01/2008
5 |
6 | =DATE(YEAR(A4),MONTH(A4)+0,15) Accrue Time VTime 5
7 | =DATE(YEAR(B6),MONTH(B6)+0,DAY(EOMONTH(B6,0))) Accrue Time VTime 5
8 |
9 | =EDATE(A4,1)
10 |
11 | =DATE(YEAR(A9),MONTH(A9)+0,15) Accrue Time VTime 5
12 | =DATE(YEAR(B11),MONTH(B11)+0,DAY(EOMONTH(B11,0))) Accrue Time VTime 5
13 |
14 | =EDATE(A9,1)
15 |
16 | =DATE(YEAR(A14),MONTH(A14)+0,15) Accrue Time VTime 5
17 | =DATE(YEAR(B16),MONTH(B16)+0,DAY(EOMONTH(B16,0))) Accrue Time VTime 5
18 |
The secrets were the EDATE function which adds a correct mathematical month to a previous date (taking the number of days of each month into account), and the EOMONTH function, which gets me the correct end-date of each month.
The resulting screen looked like this, and then I could do a copy-down from each month for any date into the future. I could also insert rows with actual vacation days, and the formulas would change correctly to compensate:
A B C D E
---------------------------------------------------------------
1 |
2 | Month Date Description Type Hrs
3 |
4 | Jan 2008
5 |
6 | 01-15-2008 Accrue Time VTime 5
7 | 01-31-2008 Accrue Time VTime 5
8 |
9 | Feb 2008
10 |
11 | 02-15-2008 Accrue Time VTime 5
12 | 02-29-2008 Accrue Time VTime 5
13 |
14 | Mar 2008
15 |
16 | 03-15-2008 Accrue Time VTime 5
17 | 03-31-2008 Accrue Time VTime 5
18 |
Previous Topic
Index
