Increment a value by a set amount per a certain time scale

[Lisa Cowan]

I am modifying a holiday spreadsheet and wish to automate it as much as
possible so there is less for me to forget to do.
So over the year I would like an individuals annual leave to be incremented
by 2.33r per month and this figure will be used in another spreadsheet which
will show how much leave they are entitled to and and equivalent hol pay.
I have looked at the various groups of Functions and don't seem to be able
to find anything.
Is it possible or not?
thanks Lisa

 

[Mike H]

Lisa,

You don't provide too much to go one here but lets try this for a start

=2.33*ROW(A1)

Entered in any cell and ragged down will cause the number to increase by
2.33 for each cell.

If you wanted a maximum value then this

=MIN(25,2.33*ROW(A1))

Which would max at 25.

Mike

Mike

 

[Lisa Cowan]

Sorry Mike I thought I had give all info but I think it is clarity that is
missing. I want this formula to be time controlled, so at the end of April I
will have accumulated 2.33r holidays and then at the end of May this will
have changed to 4.66 etc and so on and so on till we get to the end of the
financial year and then total would be 28 (obviously not taking in account
usage). I need this incrementing to happen in one cell per person so then I
can base the formula on the other spreadsheet on it.
So I need a clock of sorts that works in months and when each month is
completed it triggers the increment, based on real time and that will always
know 'when' it is regardless of how long between the s/sheet being closed.
Sorry now I feel like I am over explaining it.
Can it be done?

 

[Pete_UK]

Lisa,

you can use this:

=DATEDIF("30/03/2008",TODAY(),"m")*2.33

to give you the holiday entitlement from the beginning of April 2008
to the present (i.e. 20.97 days).

Note that because of rounding errors you will not get 28 for a full
year (27.96), so you might like to make it 2.33333333 etc.

Hope this helps.

Pete

 

[Lisa Cowan]

Thank you so much that is brilliant. Excel is such an amazing program.

 

[Pete_UK]

You're welcome, Lisa - thanks for feeding back.

Pete