Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a value by a set amount per a certain time scale
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a value by a set amount per a certain time scale
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a value by a set amount per a certain time scale
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? "Mike H" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a value by a set amount per a certain time scale
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 On Jan 5, 3:15*pm, Lisa Cowan wrote: 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? "Mike H" wrote: 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" wrote: 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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a value by a set amount per a certain time scale
Thank you so much that is brilliant. Excel is such an amazing program. :)
"Pete_UK" wrote: 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 On Jan 5, 3:15 pm, Lisa Cowan wrote: 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? "Mike H" wrote: 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" wrote: 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- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a value by a set amount per a certain time scale
You're welcome, Lisa - thanks for feeding back.
Pete On Jan 6, 9:02*am, Lisa Cowan wrote: Thank you so much that is brilliant. Excel is such an amazing program. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to increment time & subtract time? | Excel Worksheet Functions | |||
How to Increment Time using EXCEL ?? | New Users to Excel | |||
Increment formula for time | Excel Discussion (Misc queries) | |||
How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
Time and Date increment | Excel Discussion (Misc queries) |