Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And with multiple criteria
A company has employees who receive severance based on time with the company
and the payments are made monthly. If they have been employed for 1 year or more, they get 5 weeks. I know how to write the part of the formula that says if an employee is let go AND they have been with the company more than 1 year, to calculate one month's severance. I need to show the payments over each month until the # months they receive pmt is to end. What I can't figure out is what to add to the formula to make it stop when their pmts run out. Fields = Severance, Time with company, severance amount, date let go. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And with multiple criteria
Not clear from your note how many months the severance is paid out
accross. Is it over 12 months? Each month = severance/12? You will almost certainly need to use Date functions such as TODAY(), MONTH(), DAY(), etc. Perhaps an example anon customer will help to understand your quandry. Ant http://www.excel-ant.co.uk |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And with multiple criteria
If I may suggest: propose to the owner of the project or spreadsheet
that you be allowed to make the spreadsheet more intuitive. There are several good reasons for doing this: if you are responsible for ensuring payments are made, for instnace, then you need a good tool. Also, since payroll is a *very* important topic to people, and since your company is likely contractually obligated to pay those people, it is a legal exposure, not to mention an embarassment to the company, if this goes wrong. So the suggestion is, add enough fields to be able to do the job properly. If you'll post the severance pay policy this forum can help- if you'll include the frequency of pay (every two weeks, every week, every month, etc.) we can create a spreadsheet that will forecast payments based on severance date and severance policy. Post some sample data too- but remember for privacy and security to use fake names and numbers. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And with multiple criteria
Joe Smith is severed 1/1/08 with 4 years of service with the company. He is
entitled to 4 weeks severance for every year of service. Payments are made 2x month, and in the above case, would be made over 4 months. His last severance payment would be April 30. The tool will only be used by myself and management to track expenses related to severance, not to ensure payments are made. "Dave O" wrote: If I may suggest: propose to the owner of the project or spreadsheet that you be allowed to make the spreadsheet more intuitive. There are several good reasons for doing this: if you are responsible for ensuring payments are made, for instnace, then you need a good tool. Also, since payroll is a *very* important topic to people, and since your company is likely contractually obligated to pay those people, it is a legal exposure, not to mention an embarassment to the company, if this goes wrong. So the suggestion is, add enough fields to be able to do the job properly. If you'll post the severance pay policy this forum can help- if you'll include the frequency of pay (every two weeks, every week, every month, etc.) we can create a spreadsheet that will forecast payments based on severance date and severance policy. Post some sample data too- but remember for privacy and security to use fake names and numbers. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And with multiple criteria
Here's what I have now with your help:
=IF(B5="Attrition",0,IF(B5="Severance",(DATE(YEAR( H5),MONTH(H5)+DATEDIF(C5,H5,"y"),0)))) That gives me a date to stop recording severance. I entered a formula next to it: =IF(C11<"1/1/8",K5,0) that will then calc across each mo of the yr and stop when it reaches the correct stop month. But if he quit the company (attrition), the result comes back 1/1/1900 and the severance amount remains when it shouldn't. "Sandy Mann" wrote: Column A: Name, Column B: Start Date, Column C Termination Date. Last Payment date: =(DATE(YEAR(C2),MONTH(C2)+DATEDIF(B2,C2,"y"),0)) Is this waht you want? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Micki" wrote in message ... Joe Smith is severed 1/1/08 with 4 years of service with the company. He is entitled to 4 weeks severance for every year of service. Payments are made 2x month, and in the above case, would be made over 4 months. His last severance payment would be April 30. The tool will only be used by myself and management to track expenses related to severance, not to ensure payments are made. "Dave O" wrote: If I may suggest: propose to the owner of the project or spreadsheet that you be allowed to make the spreadsheet more intuitive. There are several good reasons for doing this: if you are responsible for ensuring payments are made, for instnace, then you need a good tool. Also, since payroll is a *very* important topic to people, and since your company is likely contractually obligated to pay those people, it is a legal exposure, not to mention an embarassment to the company, if this goes wrong. So the suggestion is, add enough fields to be able to do the job properly. If you'll post the severance pay policy this forum can help- if you'll include the frequency of pay (every two weeks, every week, every month, etc.) we can create a spreadsheet that will forecast payments based on severance date and severance policy. Post some sample data too- but remember for privacy and security to use fake names and numbers. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And with multiple criteria
If I understand you correctly then use "" in plaxe of 0 as in:
=IF(B5="Attrition","",IF(B5="Severance",(DATE(YEAR (H5),MONTH(H5)+DATEDIF(C5,H5,"y"),0)))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Micki" wrote in message ... Here's what I have now with your help: =IF(B5="Attrition",0,IF(B5="Severance",(DATE(YEAR( H5),MONTH(H5)+DATEDIF(C5,H5,"y"),0)))) That gives me a date to stop recording severance. I entered a formula next to it: =IF(C11<"1/1/8",K5,0) that will then calc across each mo of the yr and stop when it reaches the correct stop month. But if he quit the company (attrition), the result comes back 1/1/1900 and the severance amount remains when it shouldn't. "Sandy Mann" wrote: Column A: Name, Column B: Start Date, Column C Termination Date. Last Payment date: =(DATE(YEAR(C2),MONTH(C2)+DATEDIF(B2,C2,"y"),0)) Is this waht you want? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Micki" wrote in message ... Joe Smith is severed 1/1/08 with 4 years of service with the company. He is entitled to 4 weeks severance for every year of service. Payments are made 2x month, and in the above case, would be made over 4 months. His last severance payment would be April 30. The tool will only be used by myself and management to track expenses related to severance, not to ensure payments are made. "Dave O" wrote: If I may suggest: propose to the owner of the project or spreadsheet that you be allowed to make the spreadsheet more intuitive. There are several good reasons for doing this: if you are responsible for ensuring payments are made, for instnace, then you need a good tool. Also, since payroll is a *very* important topic to people, and since your company is likely contractually obligated to pay those people, it is a legal exposure, not to mention an embarassment to the company, if this goes wrong. So the suggestion is, add enough fields to be able to do the job properly. If you'll post the severance pay policy this forum can help- if you'll include the frequency of pay (every two weeks, every week, every month, etc.) we can create a spreadsheet that will forecast payments based on severance date and severance policy. Post some sample data too- but remember for privacy and security to use fake names and numbers. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And with multiple criteria
Can I send you a file? Is that allowed?
"Sandy Mann" wrote: If I understand you correctly then use "" in plaxe of 0 as in: =IF(B5="Attrition","",IF(B5="Severance",(DATE(YEAR (H5),MONTH(H5)+DATEDIF(C5,H5,"y"),0)))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Micki" wrote in message ... Here's what I have now with your help: =IF(B5="Attrition",0,IF(B5="Severance",(DATE(YEAR( H5),MONTH(H5)+DATEDIF(C5,H5,"y"),0)))) That gives me a date to stop recording severance. I entered a formula next to it: =IF(C11<"1/1/8",K5,0) that will then calc across each mo of the yr and stop when it reaches the correct stop month. But if he quit the company (attrition), the result comes back 1/1/1900 and the severance amount remains when it shouldn't. "Sandy Mann" wrote: Column A: Name, Column B: Start Date, Column C Termination Date. Last Payment date: =(DATE(YEAR(C2),MONTH(C2)+DATEDIF(B2,C2,"y"),0)) Is this waht you want? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Micki" wrote in message ... Joe Smith is severed 1/1/08 with 4 years of service with the company. He is entitled to 4 weeks severance for every year of service. Payments are made 2x month, and in the above case, would be made over 4 months. His last severance payment would be April 30. The tool will only be used by myself and management to track expenses related to severance, not to ensure payments are made. "Dave O" wrote: If I may suggest: propose to the owner of the project or spreadsheet that you be allowed to make the spreadsheet more intuitive. There are several good reasons for doing this: if you are responsible for ensuring payments are made, for instnace, then you need a good tool. Also, since payroll is a *very* important topic to people, and since your company is likely contractually obligated to pay those people, it is a legal exposure, not to mention an embarassment to the company, if this goes wrong. So the suggestion is, add enough fields to be able to do the job properly. If you'll post the severance pay policy this forum can help- if you'll include the frequency of pay (every two weeks, every week, every month, etc.) we can create a spreadsheet that will forecast payments based on severance date and severance policy. Post some sample data too- but remember for privacy and security to use fake names and numbers. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And with multiple criteria
If it is not too big then yes you can send a file to my e-mail address by
altering it as it says in my signature - maininator.com is just a spam trap. It is quite late here in the UK so I may not be able to look at it until tomorrow. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Micki" wrote in message ... Can I send you a file? Is that allowed? "Sandy Mann" wrote: If I understand you correctly then use "" in plaxe of 0 as in: =IF(B5="Attrition","",IF(B5="Severance",(DATE(YEAR (H5),MONTH(H5)+DATEDIF(C5,H5,"y"),0)))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Micki" wrote in message ... Here's what I have now with your help: =IF(B5="Attrition",0,IF(B5="Severance",(DATE(YEAR( H5),MONTH(H5)+DATEDIF(C5,H5,"y"),0)))) That gives me a date to stop recording severance. I entered a formula next to it: =IF(C11<"1/1/8",K5,0) that will then calc across each mo of the yr and stop when it reaches the correct stop month. But if he quit the company (attrition), the result comes back 1/1/1900 and the severance amount remains when it shouldn't. "Sandy Mann" wrote: Column A: Name, Column B: Start Date, Column C Termination Date. Last Payment date: =(DATE(YEAR(C2),MONTH(C2)+DATEDIF(B2,C2,"y"),0)) Is this waht you want? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Micki" wrote in message ... Joe Smith is severed 1/1/08 with 4 years of service with the company. He is entitled to 4 weeks severance for every year of service. Payments are made 2x month, and in the above case, would be made over 4 months. His last severance payment would be April 30. The tool will only be used by myself and management to track expenses related to severance, not to ensure payments are made. "Dave O" wrote: If I may suggest: propose to the owner of the project or spreadsheet that you be allowed to make the spreadsheet more intuitive. There are several good reasons for doing this: if you are responsible for ensuring payments are made, for instnace, then you need a good tool. Also, since payroll is a *very* important topic to people, and since your company is likely contractually obligated to pay those people, it is a legal exposure, not to mention an embarassment to the company, if this goes wrong. So the suggestion is, add enough fields to be able to do the job properly. If you'll post the severance pay policy this forum can help- if you'll include the frequency of pay (every two weeks, every week, every month, etc.) we can create a spreadsheet that will forecast payments based on severance date and severance policy. Post some sample data too- but remember for privacy and security to use fake names and numbers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
How to retrieve multiple values in multiple rows with one criteria | Excel Discussion (Misc queries) | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |