#313289 - 28/08/2008 16:16
Excel spreadsheet creation help: Automatic date accruals
|
carpal tunnel
Registered: 20/12/1999
Posts: 31597
Loc: Seattle, WA
|
Sometimes I need to carefully track my vacation time usage, since I travel to a lot of Sci Fi and Filk conventions and frequently run up against the bleeding edge of the number of hours I can take off in a given time period. I often need to plan these days far in advance, so simply looking at how much vacation time I've accrued *now* isn't useful, I need to know how much vacation time I *will have accrued* as of a date several months from now (including any time I may be planning on taking between now and then). Perfect job for a spreadsheet, of course. I've been keeping track of it by hand so far, but a spreadsheet would be much nicer. Does anyone know how to have Excel automatically insert rows and do accruals based on dates that are related to the position within a month? Let me be more specific. I want a row to automatically appear on the spreadsheet for the 15th day of every month, and for the last day of every month. That's when the vacation time accruals are processed: I accrue 5 hours of vacation time with each paycheck, or 10 hours per month, working out to 15 days per year. A given section of my desired spreadsheet might look something like this:
Date Event Change FHoliday Total SickTime Total Vtime Total
---------- ------------- ----------- -------------- -------------- -----------
2008 03 10 Consonance Vtime -8 16 73.26 2
2008 03 15 Accrue Time Vtime +5 16 73.26 7
2008 03 21 Sick Day Sick -8 16 65.26 7
2008 03 27 Sick Day Sick -8 16 57.26 7
2008 03 31 Accrue Time Vtime +5 16 57.26 12
2008 04 03/04 FilkOntario FHoliday -16 0 57.26 12
2008 04 07 FilkOntario Vtime -8 0 57.26 4
2008 04 15 Accrue Time Vtime +5 0 57.26 9
2008 04 30 Accrue Time Vtime +5 0 57.26 14
Is there any easy way to make the "Accrue Time" lines appear automatically in the spreadsheet?
|
|
Top
|
|
|
|
|
#313290 - 28/08/2008 16:41
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tfabris]
|
carpal tunnel
Registered: 24/12/2001
Posts: 5528
|
You'd have to write some sort of VB.net module to do what you want I think.
As for your actual holiday system ewww. Its a bit complicated isn't it? I get 20 days each year. It gets allocated in January and I've got until December to use it all up. I can take those days whenever I want. Must have 2 weeks off consecutively at some point in the year however.
|
|
Top
|
|
|
|
|
#313292 - 28/08/2008 16:49
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tman]
|
carpal tunnel
Registered: 20/12/1999
Posts: 31597
Loc: Seattle, WA
|
Yes, the complexity sucks, agreed. I especially dislike having to track things like sick time and floating holidays separately. I've seen some companies which do a generic "flex time" that encompasses all types of days off, which has advantages and disadvantages of its own. On the good side, my current company's vacation time carries over from previous years (albeit with a 15-day cap), in contrast with my prior employer's use-it-or-lose-it policy. Anyway, it's all I've got to work with at the moment. 
|
|
Top
|
|
|
|
|
#313293 - 28/08/2008 16:54
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tfabris]
|
carpal tunnel
Registered: 20/12/1999
Posts: 31597
Loc: Seattle, WA
|
And may I just say the new Microsoft Office "Ribbon" or "Edit Bar" or whatever the fuck it's called SUCKS. Back in the old days, I used to be able to figure out what the shortcut keys were for a given feature simply by pulling down the menu and looking. Now there's no menu to pull down and I have no easy reference for seeing the shortcut key for something.
|
|
Top
|
|
|
|
|
#313322 - 29/08/2008 00:07
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tfabris]
|
pooh-bah
Registered: 20/01/2002
Posts: 2085
Loc: New Orleans, LA
|
Heh, and it used to be a snap for secretaries to modify it to their needs. Now you need to know XML. Major suckage.
|
|
Top
|
|
|
|
|
#313327 - 29/08/2008 01:37
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tfabris]
|
pooh-bah
Registered: 19/09/2002
Posts: 2494
Loc: East Coast, USA
|
Why not start with an Excel file pre-populated with the date of every pay day on its own row. Find a copy of the pay schedule on the intranet and copy/paste. Fill column C (Change) with 5.
Column F (Vtime Total) would be a running total. ie: F4 is =F3+C4. When you want to take off 4/8/08, just insert a new row 8. Excel will fill F8 with the running total formula. In C8, enter -8.
This would require separate Change columns for FHoliday, SickTime, and Vtime. Instead, Change could be purely numeric and the preceding column could be "Type" to indicate which of the three off times. The running totals could use an IF to check Type and see if the value in Change applies to them or not.
But you probably knew this, because no questions on this BBS are actually easy enough for me to answer.
_________________________
- FireFox31 110gig MKIIa (30+80), Eutronix lights, 32 meg stacked RAM, Filener orange gel lens, Greenlights Lit Buttons green set
|
|
Top
|
|
|
|
|
#313340 - 29/08/2008 15:12
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: FireFox31]
|
carpal tunnel
Registered: 20/12/1999
Posts: 31597
Loc: Seattle, WA
|
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 |
|
|
Top
|
|
|
|
|
#313350 - 29/08/2008 21:16
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: FireFox31]
|
old hand
Registered: 16/02/2002
Posts: 867
Loc: Oxford, UK
|
no questions on this BBS are actually easy enough for me to answer. And on that note... I'm still waiting for someone to turn the Off Topic area into an audio book that I can play while sleeping!  Edit: And it should be read by Holly (both Norman Lovett and Hattie Hayridge) from Red Dwarf.
Edited by AndrewT (29/08/2008 21:21)
|
|
Top
|
|
|
|
|
#313354 - 30/08/2008 00:02
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: AndrewT]
|
carpal tunnel
Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
|
Your request was fun to work out. Attached is a spread sheet that works pretty well. Column C has all the logic. There is a readme on the second sheet. Notes: 1. Accrual dates must be entered to get benefit 2. After inserting a new line, the "Current Balance" summation will break until the gap is filled. This can be repaired by filling, either down, up, or by cut and paste. 3. You can literally grab the bottom row, fill down, then delete any rows not desired.
Date HOURS SPENT Balance Comment
0 << Starting Balance
8/2/2008 0
8/15/2008 5 Time Accrued
8/20/2008 5
8/29/2008 5
8/30/2008 5
8/31/2008 10 Time Accrued
9/1/2008 8 2 Planned day off.
9/2/2008 2
9/15/2008 7 Time Accrued
9/16/2008 7
9/17/2008 7
9/29/2008 7
9/30/2008 12 Time Accrued
10/1/2008 12
Edit: Readme should say... If you choose to move columns A or C, you will need to adjust the offsets in the formulas.
Edited by gbeer (30/08/2008 00:06)
_________________________
Glenn
|
|
Top
|
|
|
|
|
#313380 - 31/08/2008 20:14
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: gbeer]
|
carpal tunnel
Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
|
Also the "Analysis Tool Pack" add-in must be installed to have the additional date functions.
_________________________
Glenn
|
|
Top
|
|
|
|
|
#313381 - 31/08/2008 21:30
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: gbeer]
|
carpal tunnel
Registered: 20/12/1999
Posts: 31597
Loc: Seattle, WA
|
Also the "Analysis Tool Pack" add-in must be installed to have the additional date functions. That's what it said, but I noticed I didn't actually need it. Perhaps it was because I was on Office 2007.
|
|
Top
|
|
|
|
|
#313382 - 31/08/2008 21:33
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: gbeer]
|
carpal tunnel
Registered: 20/12/1999
Posts: 31597
Loc: Seattle, WA
|
Your request was fun to work out. Attached is a spread sheet that works pretty well. Interesting! I've saved it off and I'm looking at it closely to see what you did. Thanks!
|
|
Top
|
|
|
|
|
#313383 - 31/08/2008 23:14
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tfabris]
|
carpal tunnel
Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
|
New file, Slightly different formatting.
Dates are formatted conditionally, Weekends in blue text, Accrual dates with green background.
Broke out accrual into separate column, and revised balance formula to do sums only. Added grouping so past months can be collapsed.
One of the conditional formatting terms is a little clumsy. Excel 2003 doesn't seem to recognize EOMONTH in that context. So it had to be indirect.
Attachments
tonys_leave-2.xls (130 downloads)
_________________________
Glenn
|
|
Top
|
|
|
|
|
#313384 - 31/08/2008 23:17
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tfabris]
|
carpal tunnel
Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
|
Also the "Analysis Tool Pack" add-in must be installed to have the additional date functions. That's what it said, but I noticed I didn't actually need it. Perhaps it was because I was on Office 2007. Add-in's only need to be installed once. They don't carry with the spreadsheet.
_________________________
Glenn
|
|
Top
|
|
|
|
|
#313411 - 01/09/2008 22:54
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: gbeer]
|
carpal tunnel
Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
|
The reason for using OFFSET cell references is that it defeats the spreadsheets standard referencing behavior when new rows are inserted.
d3=d2+c3 will work to create a running balance. When a row is inserted Excel will repair all references to bridge around the inserted row. After the new info is entered in that row, the entire running balance column has to be repaired starting from the inserted row down.
d3=offset(d3,-1,0)+offset(d3,0,-1) will create the same running balance. When a new row is inserted, Excel will still fix all cell references, but since the cell only references itself, It still pulls values from the desired places. All that need to be done is to fill down into the new row, and edit the values as desired.
The downside to offsets is, sometimes, like when a column is inserted between c and d, Excel won't fix the references.
Thinking about that, in the second spreadsheet, maybe I should have used a combination, offset references for vertical, and relative references for the horizontal.
d3=offset(d3,-1,0)+b3-c3
_________________________
Glenn
|
|
Top
|
|
|
|
|
#313412 - 01/09/2008 23:02
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: gbeer]
|
carpal tunnel
Registered: 20/12/1999
Posts: 31597
Loc: Seattle, WA
|
Yeah, I caught that trick. Very cool!
What I'd like to know, though, is how to make a formula that says "me".
So I could do like "offset (me, 1, 1)"
Where "me" is the current cell, period, regardless of where it is on the spreadsheet.
I know that I could do like "offset (c3, 1, 1)" when it's in cell C3, and then the "c3" would change each time I copied the cell, but there were some other situations I could have used something like that.
I looked around in a lot of places for something like that and couldn't find it.
|
|
Top
|
|
|
|
|
#313416 - 02/09/2008 00:18
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tfabris]
|
carpal tunnel
Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
|
"me" can be written as: INDIRECT(ADDRESS(ROW();COLUMN())) so: OFFSET(INDIRECT(ADDRESS(ROW();COLUMN()));1;1)
_________________________
Bitt Faulk
|
|
Top
|
|
|
|
|
#313418 - 02/09/2008 00:28
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: wfaulk]
|
carpal tunnel
Registered: 20/12/1999
Posts: 31597
Loc: Seattle, WA
|
Fragging brilliant. Thank you!
|
|
Top
|
|
|
|
|
#313426 - 02/09/2008 02:27
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: tfabris]
|
carpal tunnel
Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
|
So the point of "=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,1)" is to create a formula that can be cut and pasted as text and the cell reference would never need to be corrected?
BTW "=INDIRECT(ADDRESS(ROW(),COLUMN()))" will produce a circular reference. Generally a bad thing.
_________________________
Glenn
|
|
Top
|
|
|
|
|
#313427 - 02/09/2008 02:45
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: gbeer]
|
carpal tunnel
Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
|
The only thing I dislike about the offset function is that the operands are kind of backwards. offset(A1,row,column) A1 being ColumnRow notation.
_________________________
Glenn
|
|
Top
|
|
|
|
|
#313428 - 02/09/2008 03:30
Re: Excel spreadsheet creation help: Automatic date accruals
[Re: gbeer]
|
carpal tunnel
Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
|
So the point of ... is to create a formula that can be cut and pasted as text and the cell reference would never need to be corrected? Well, in order to reference the previous cell even if it gets inserted. You can't use any of Excel's built-in reference syntaxes (syntaxen? syntaces?) to do that because it will rewrite them in ways you don't want. But yes.
_________________________
Bitt Faulk
|
|
Top
|
|
|
|
|
|
Previous Topic
Index