1. J

    IF formula based on a value between 2 numbers, and also matching specific criteria

    Hello, I have a group of employees that have results for various metrics. I want to assign a Score for each of these metrics based on specific values that their result is between. For example, one metric might be quality, and I might have scores 1-5 where 5 is anything greater than 98%, 4 is...
  2. S

    Filter Spread sheet by date range and calculate difference between dates

    Hi I have a large spread sheet that needs to be analysed at the end of each month. I need some help trying to make this much more efficient. Currently on hire dates are in a column (R) and off hire dates in the next (S). R S...
  3. A

    Need to solve a mathematical problem and I must use two fonctions to do it.

    Hi, I need to solve this using 2 fonctions (not 1, not 3). You hire an employee to pick up tomatos.If he picks up 8 cases an hour, 8 hours a day, 6 days a week, but you guarantee him a minimum of 4 weeks, how many days do you hire him for 1000 cases? I'm really stuck with this one and I cant...
  4. H

    Seniority based on hire date and age

    Im trying to get excel to rank the employees in seniority based on hire date and if hire date then rake them based on the oldest <colgroup><col span="2"><col><col><col><col></colgroup><tbody> NAME HIRE DATE DOB BILLY 9/27/1984 10/9/1960 TONY 10/3/1987 7/1/1962 WILLIAM 8/5/1989...
  5. A

    split data across columns if the data is in one column only

    My data is in one column as follows: NAME DATE OF BIRTH HIRE DATE TERMINATION DATE NAME DATE OF BIRTH HIRE DATE TERMINATION DATE NAME DATE OF BIRTH HIRE DATE TERMINATION DATE I need the name in one column and the date of birth in next column followed with hire date and termination date. How can...
  6. M

    PTO Accrual Tracker

    I am trying to calculate PTO for my company of 600+ employees. I have used a few of the formulas posted here over the years, but can't seem to get it work per our policy Example: If an employee has a hire date of 6/3/18, from 12/16/18 through today would equal 6 pay periods at the <1 year rate...
  7. S

    calculating vaction accrual by hire date

    I have been asked to calculate vacation accrual for the end of the year. vacation is earned at the hire date. I was trying to calculate how much is earned monthly.
  8. B

    working days between hire and term dates

    I have an employees start date in cell H11 and the term date in cell I11. In cells J6:U6, I have the months of 1/1/2019 - 12/1/2019. In cells J3:U3, I'm trying to calculate the number of working days that employee worked that month given their starting and term date. If the employee is...
  9. E

    Simplified SUMIF

    Hi everyone - Happy Monday! Apologies in advance as this is a little convoluted. Wanted to see if anyone has advice on a formula I'm about to write in regards to a SUMIF. Basically, I am working with 4 quarters of result data (result in my vernacular means a dollar payout for context). Each...
  10. G

    Using EDATE

    Hello. I need to calculate what the next anniversary date is if today's date is more than 365 days than the hire date: Creating a budget for 2019. The information below is regarding an employee's hire date which will affect a merit increase. Current formula: A1 = reflects the Hire Date the...
  11. A

    IF Statement for Employee Turnover Identification

    Hello, I'm trying to determine if an employee is a new hire or terminated based on 3 criteria. I've tried creating a column concatenating the month and year to use an if(and statement but can't figure it out... I'd really appreciate any help or direction. 1. If hire month & year = Date in cell...
  12. B

    Using hire date to convert either a year later or the first of the year based on the hire date

    Hello, I am trying to do a vacation accrual worksheet base off of a start date, or the first of the year. So I need the cell to populate either the hire date or 1/1/18 if they have already been employed for one year. So in Cell B5 I have the hire date in cell C5 I have the termination date (or...
  13. I

    IF Statements

    Hello - this is a great forum, can anyone assist with the following? <tbody> Assumption 5% Raise if date of termination is blank and hire date is before 7/1/17, then 5%, if date of termination is blank and hire date is b/w 8/1/17 and 3/31/18, then...
  14. W

    Equipment Costs based on Start and End Dates

    Hi, I'm trying to forecast weekly cost of equipment and rounding up the weekly costfor a full 7 day period so if a hire commences on a Thursday or a FridayI'd like to round up to the Monday howver I cannot seem to get the formula towork: B3 = Hire From Date of 31 July 2018 C3 = Anticipated...
  15. M

    Vacation accrual formula HELP!

    Hey guys! Im new here and am getting really frustrated trying to make my formula work. I need to accrue vacation time for my full time employees. At hire date anniversary, they get 40hrs and accrue 3.33hrs per pay period. I need to figure out the formula for my balance column at the end, AF6...
  16. C

    Anyone for hire on small project?

    Are we allowed to hire someone for a small project? The details are too much to post for help in smaller segments, so I'd rather explain them one-on-one and work on the project together. If this is allowed here, please reach out to me ASAP, I need this done by Tuesday. Penny
  17. M

    If statement help??

    I created the below formula to calculate length of our hiring process. It will show pending, if the hire hasn't completed yet. IF(R2="HIRED",IFERROR(DATEDIF(AF2,AV2,"D"),""),"PENDING") Now, I want to account for hiring delays that are created by the candidate. Meaning, they're not looking to...
  18. T

    Formula to spread cost of hired equipment between weekly periods?

    Hi, I'm trying to compare budget against actual against several cost codes. Part of the analysis requires me to look at hired equipment and I am required to compare spends vs budgets in weekly periods. I am trying (based on the anticipated or known on and off hire dates) to "spread" the...
  19. V

    Transpose range of cells based on the whether a cell repeats

    I have an input sheet with a lot of names in Column A which repeats (Maximum 3 times) and rest of the cells (from column C) are different. I need to generate a sheet with unique names and rest of the cells transposed. INPUT SHEET Employee ID Date Of Joining Effective Date...
  20. J

    Dynamic Range Name

    Looking to tie the range name to cell value For example <tbody> A B C 1 2 A_Sanchez 3 1 4 3 5 7 6 6 7 2 8 1 9 </tbody> I want the name of Range B1:B8 to equal the value of B:2 This way when I Fire A_Sanchez and Hire someone else I can...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back