Using Excel, what would be the formula to calculate the balance of "comp time" earned?

Question

"Comp time" is earned within a year, which is divided into 10 time periods; However, if the "comp time" earned within a specific time period is not used within the subsequent 10 time periods from which it was earned, the balance of "comp time" earned 10 time periods ago will be lost or subtracted from the total balance.

E.g. 10 hours is earned on day 1 of the 1st time period. 5 hours is used during time period 5 leaving a balance of 5. Another 10 hours is earned during time period 7 . New balance = 15. At the end of the 10th time period the remaining 5 hours earned on day 1 is lost leaving a balance of 10 hours.

I would like to be able to track hours earned during any time period and know what the balance is at any time and have the program automatically delete unused hours from the balance at the appropriate time.

Any ideas?

Answer 1

If there are no restrictions on the amount of comp time you can earn or use in any given period, I would ignore the periods and use the Now() function:
Set your format to suppress 0's.
Format column A to your preferred date display.
In cell A!, enter =Now()
in A2 enter date
in B2 enter earned
in C2 enter used
in D3 enter =if(Now()-A3<366,B3,0)
in E3 enter =if(Now()-A3<366,C3,0)
Select D3 & E3 and Fill Down for a hundred or so rows.
Let's say you fill down to row 200.
select D201 and click on ∑ on your toolbar; hit [enter]. This will give you the sum of column D
Now fill across to E201 to sum column E.
In F201 enter =D201-E201
Move F201 to B1
in C1 enter hours available.
select A3 and execute "Freeze Titles". This will keep rows 1 and 2 always in view.
When you do your data entry, beginning with Row 3, COPY A1 to A(n) using "Paste special--values only"
Enter hours earned into B(n) and hours used into C(n)
B1 will display your running total, with outdated data filtered out.

When you boot the spreadsheet, A1 will always show today's date. You can use ctrl ↓ in the date column to ge to your last entry.

To enter data back to a starting date you will have to enter your dates manually.

Answer 2

You could actually do this in excel, but you would need some very basic programming knowledge and be very good at keeping tabs on logic.

First of you need a counter, something that will count the time periods for you, hence you have a column labelled 1 to 10

At this point i can only make assumptions as the question is a bit vague and confusing.

There will be a field which we will call "TIME LEFT" and another field called "TIME CONSUMED"

in the time left field you type in

= if(COUNTER/10 < 10, 10 - TIME CONSUMED, 10 + "TIME LEFT PREVIOUS ROW" - "TIME CONSUMED")

*note everything in capitals means the cell reference i.e. if the counter field was in the A column then you would have =if(A1 = 10... etc

to explain this line of code

excel uses the if statement in this format
=if(statement, true value, false value)

the statement here is
COUNTER = 10

if this statement is true then we have
the 10 - time consumed (the time balance has reset to 10)

if this statement is false you will have
the 10 + the time balance remaining from the previous row - time consumed for this row (the time balance has been brought over)

i think thats what u needed. hope it helps if not just post some more info

Answer 3

it really is sad even as the idea of existence revolves around the dollar. How about in basic terms taking it as your opinion may help answer an significant question quite than putting a dollar quantity on your answer,, and once you've a situation being right here, then why come?