Excel Formula Cannot Figure Out

 Posts: 69
 Joined: Wed Dec 13, 2017 10:24 am
Excel Formula Cannot Figure Out
Hello Everyone,
I am trying to make an excel spreadsheet that can track PTO.
I am trying to get it to base everything off the current date.
Here is what we have
Up to and including year 2 is 128 hours
Beginning year 3 is 168
Beginning year 8 is 2920
Beginning year 12 is 4380
If you do not know how to write the equation, do you know a site to visit that I could ask that question in as well?
I am getting stumped on this so any help would be appreciated!
Thanks!
I am trying to make an excel spreadsheet that can track PTO.
I am trying to get it to base everything off the current date.
Here is what we have
Up to and including year 2 is 128 hours
Beginning year 3 is 168
Beginning year 8 is 2920
Beginning year 12 is 4380
If you do not know how to write the equation, do you know a site to visit that I could ask that question in as well?
I am getting stumped on this so any help would be appreciated!
Thanks!
Re: Excel Formula Cannot Figure Out
The formula for today's date is =today()
However, I cannot tell what equation you are trying to write based on your post.
However, I cannot tell what equation you are trying to write based on your post.

 Posts: 69
 Joined: Wed Dec 13, 2017 10:24 am
Re: Excel Formula Cannot Figure Out
I am trying to write an equation that says if todays date their hire date is less than 1095 (which is 3 years of days) than the answer would be 128, if it is over 1095 but less than 2920(8 years) than the answer is 168, if it is between 2920 but less than 4380(12 years) than it is 208, if it is over 4380, than the answer would be 248. I just dont know how to string an If statement that long or if an if statement is the way to go.
if a1 is the hire date
The formula =If(Today()A1>1095, 168, 128) works just fine for the first group but I cant add on to the formula any further without help from someone that knows what they are doing.
if a1 is the hire date
The formula =If(Today()A1>1095, 168, 128) works just fine for the first group but I cant add on to the formula any further without help from someone that knows what they are doing.
Re: Excel Formula Cannot Figure Out
You could nest several "IF" formulas by replacing the "128" with a complete "IF" formula, and then nest one in that formula, and so on.
You could also calculate years of employment (use "rounddown" to get a whole number) and use that value for a "vlookup" to a small table of the correct answers.
TNWoods
You could also calculate years of employment (use "rounddown" to get a whole number) and use that value for a "vlookup" to a small table of the correct answers.
TNWoods
Last edited by TNWoods on Thu Dec 19, 2019 12:21 pm, edited 1 time in total.

 Posts: 5
 Joined: Tue Sep 10, 2019 9:30 am
Re: Excel Formula Cannot Figure Out
You can set it up as a series of if then statements. I used 3 weeks, 4 weeks, 5 weeks, and 6 weeks as my PTO numbers but you can put in whatever you want.
=IF(TODAY()A1>4380,240,IF(TODAY()A1>2920,200,IF(TODAY()A1>1095,160,120)))
=IF(TODAY()A1>4380,240,IF(TODAY()A1>2920,200,IF(TODAY()A1>1095,160,120)))
Re: Excel Formula Cannot Figure Out
I would string multiple if functions together. For the false result part, you add in another if function. Either start with greater than the top number or lower than the bottom number.Mr400meterdash wrote: ↑Thu Dec 19, 2019 12:09 pmI am trying to write an equation that says if todays date their hire date is less than 1095 (which is 3 years of days) than the answer would be 128, if it is over 1095 but less than 2920(8 years) than the answer is 168, if it is between 2920 but less than 4380(12 years) than it is 208, if it is over 4380, than the answer would be 248. I just dont know how to string an If statement that long or if an if statement is the way to go.
if a1 is the hire date
The formula =If(Today()A1>1095, 168, 128) works just fine for the first group but I cant add on to the formula any further without help from someone that knows what they are doing.
=IF(Today()A1<1095, 128, IF(Today()A1<2920, 168, IF(Today()A1<4380, 208, 248)))
You could also go the other way and start with the lager number 4380 and go A1>4380
Re: Excel Formula Cannot Figure Out
You don't need to nest the IF statement, you can instead use the function "IFS" to evaluate multiple if statements:
https://exceljet.net/excelfunctions/excelifsfunction
https://exceljet.net/excelfunctions/excelifsfunction
Re: Excel Formula Cannot Figure Out
Something like this:Mr400meterdash wrote: ↑Thu Dec 19, 2019 12:09 pmI am trying to write an equation that says if todays date their hire date is less than 1095 (which is 3 years of days) than the answer would be 128, if it is over 1095 but less than 2920(8 years) than the answer is 168, if it is between 2920 but less than 4380(12 years) than it is 208, if it is over 4380, than the answer would be 248. I just dont know how to string an If statement that long or if an if statement is the way to go.
if a1 is the hire date
The formula =If(Today()A1>1095, 168, 128) works just fine for the first group but I cant add on to the formula any further without help from someone that knows what they are doing.
=if(x>10,1095,if(x>9,168,128))
Use your numbers though.

 Posts: 69
 Joined: Wed Dec 13, 2017 10:24 am
Re: Excel Formula Cannot Figure Out
That worked!
Thank you!
Thank you!
Re: Excel Formula Cannot Figure Out
I was all psyched to learn about the "IFS" and then I see it's only available for Office 2019. I'm a couple versions back...
I'll remember it for the future when we get upgraded. If I'm still working.
TNWoods
I'll remember it for the future when we get upgraded. If I'm still working.
TNWoods

 Posts: 288
 Joined: Thu Oct 24, 2013 12:32 am
Re: Excel Formula Cannot Figure Out
Wish I worked at your company, 4380 hours is a lot of PTO!
You can nest logical IFs, but personally I think complicated Excel formulas are a big problem with Excel, and using a complicated formula for anything related to HR and comp puts a business at risk. Especially when you start copying formulas and it's easy to get relative vs absolute cell references wrong.
I would make a column that computes the number of years worked, and then I would make a table, maybe on another sheet, that has years of service in one column and PTO hours in the next column. Then you can use VLOOKUP in another column to pull the right pto value out of the table.
You can nest logical IFs, but personally I think complicated Excel formulas are a big problem with Excel, and using a complicated formula for anything related to HR and comp puts a business at risk. Especially when you start copying formulas and it's easy to get relative vs absolute cell references wrong.
I would make a column that computes the number of years worked, and then I would make a table, maybe on another sheet, that has years of service in one column and PTO hours in the next column. Then you can use VLOOKUP in another column to pull the right pto value out of the table.
Re: Excel Formula Cannot Figure Out
Another advantage is that it puts the PTO thresholds in one place. If your company's PTO policy changes, you update one spreadsheet with the new values & you're done  every other spreadsheet that references it starts using the new values. If you have those PTO thresholds embedded in individual formulae spread all over the place, you have a much bigger job ahead of you.lotusflower wrote: ↑Thu Dec 19, 2019 12:35 pmWish I worked at your company, 4380 hours is a lot of PTO!
You can nest logical IFs, but personally I think complicated Excel formulas are a big problem with Excel, and using a complicated formula for anything related to HR and comp puts a business at risk. Especially when you start copying formulas and it's easy to get relative vs absolute cell references wrong.
I would make a column that computes the number of years worked, and then I would make a table, maybe on another sheet, that has years of service in one column and PTO hours in the next column. Then you can use VLOOKUP in another column to pull the right pto value out of the table.
Re: Excel Formula Cannot Figure Out
FYI, almost all Excel 2019 functions including IFS are supported in Google Sheets.
 neurosphere
 Posts: 3584
 Joined: Sun Jan 17, 2010 1:55 pm
Re: Excel Formula Cannot Figure Out
First, I'm very excited to have learned today about "IFS". Had I known about it just yesterday, it would have save me an hour working on a withholding calculator! E.g. "IFS adjusted wage income is between X and Y then Z, if between A and B then C" etc. Second, it's great that it works in Google Sheets as well. Thanks for the info.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

 Posts: 860
 Joined: Tue May 15, 2018 10:14 pm
Re: Excel Formula Cannot Figure Out
I can't help but think you have an error in these numbers.Mr400meterdash wrote: ↑Thu Dec 19, 2019 12:01 pmHello Everyone,
I am trying to make an excel spreadsheet that can track PTO.
I am trying to get it to base everything off the current date.
Here is what we have
Up to and including year 2 is 128 hours
Beginning year 3 is 168
Beginning year 8 is 2920
Beginning year 12 is 4380
If you do not know how to write the equation, do you know a site to visit that I could ask that question in as well?
I am getting stumped on this so any help would be appreciated!
Thanks!
2920/8 = 365
4380/12 = 365
For the first two, I think you're saying the number of PTO hours per year a person gets if they have been working <=2 years, or between 2 and 3 years.
For the last two, I think you're just saying the number of days which are in so many years.
Why not just tell us what the rule is for how much PTO someone gets when they have working how many years? Maybe we need to know whether PTO hours are "use it or lose it" on the anniversary date of employment vs they continue to accrue. Does an employee who has been there only 6 months have the exact same amount of PTO as someone who has been there 2 years? If not, what's the formula? I can't believe it's a simple as you put in the 4 lines of information.
Thanks.
If we know more details I think we can make a spreadsheet formula.
Just because you're paranoid doesn't mean they're NOT out to get you.
Re: Excel Formula Cannot Figure Out
As someone who has painfully nested far too many functions, you just made my day. Thank you, thank you.mlipps wrote: ↑Thu Dec 19, 2019 12:21 pmYou don't need to nest the IF statement, you can instead use the function "IFS" to evaluate multiple if statements:
https://exceljet.net/excelfunctions/excelifsfunction
Re: Excel Formula Cannot Figure Out
=CHOOSE(MIN(YEARFRAC(A1,TODAY(),1),12)+1,128,128,128,168,168,168,168,168,208,208,208,208,248)
Money is fungible 
Abbreviations and Acronyms
Re: Excel Formula Cannot Figure Out
If you like IFS, also consider SWITCH( ) and CHOOSE( )Fresh Air wrote: ↑Thu Dec 19, 2019 2:35 pmAs someone who has painfully nested far too many functions, you just made my day. Thank you, thank you.mlipps wrote: ↑Thu Dec 19, 2019 12:21 pmYou don't need to nest the IF statement, you can instead use the function "IFS" to evaluate multiple if statements:
https://exceljet.net/excelfunctions/excelifsfunction

 Posts: 69
 Joined: Wed Dec 13, 2017 10:24 am
Re: Excel Formula Cannot Figure Out
You are correct on me having my numbers incorrect. Sorry!GrowthSeeker wrote: ↑Thu Dec 19, 2019 2:15 pmI can't help but think you have an error in these numbers.Mr400meterdash wrote: ↑Thu Dec 19, 2019 12:01 pmHello Everyone,
I am trying to make an excel spreadsheet that can track PTO.
I am trying to get it to base everything off the current date.
Here is what we have
Up to and including year 2 is 128 hours
Beginning year 3 is 168
Beginning year 8 is 2920
Beginning year 12 is 4380
If you do not know how to write the equation, do you know a site to visit that I could ask that question in as well?
I am getting stumped on this so any help would be appreciated!
Thanks!
2920/8 = 365
4380/12 = 365
For the first two, I think you're saying the number of PTO hours per year a person gets if they have been working <=2 years, or between 2 and 3 years.
For the last two, I think you're just saying the number of days which are in so many years.
Why not just tell us what the rule is for how much PTO someone gets when they have working how many years? Maybe we need to know whether PTO hours are "use it or lose it" on the anniversary date of employment vs they continue to accrue. Does an employee who has been there only 6 months have the exact same amount of PTO as someone who has been there 2 years? If not, what's the formula? I can't believe it's a simple as you put in the 4 lines of information.
Thanks.
If we know more details I think we can make a spreadsheet formula.
year 1end of 2 is 128
beginning of year 3 to end of year 7 is 168
beginning of year 8 to end of year 11 is 208
beginning of year 12 to forever is 248
If a person joins in October they get 3 months worth of PTO so 32 hours. Rollover is 5 days. PTO days accrue annually so we get all days Jan 1 of the next year.
The problem I am running into at the moment is having the formula understand that it cant change the numbers until the next year.
For example. Lets say the hire date was 122017. This year I should still have 208 hours of PTO but as soon as tomorrow hits, it will give me 40 more hours.
For everyone's knowledge, this is just a prototype of something I may pitch to HR. It is not a big company or anything like that but thought I could reduce costs for us by making something simpler. I also love playing around with excel so thought why not. I would create a database for this if it was going to be a true idea but I like making things in excel first.
Thanks!

 Posts: 860
 Joined: Tue May 15, 2018 10:14 pm
Re: Excel Formula Cannot Figure Out
I'm not sure I understand what "rollover" means in this context. Does this mean that if as of Dec 31 of a given year you still have, say, 11 days of unused PTO then on Jan 1 you have your full allotment for the new year, plus 5 of the 11 unused days from the previous year? But if you had used up all your days as of Dec 31, then on Jan 1 you'd get just the allotment for the new year?Mr400meterdash wrote: ↑Thu Dec 19, 2019 3:17 pmRollover is 5 days. PTO days accrue annually so we get all days Jan 1 of the next year.
All the more reason to get this exactly right, so it works perfectly. Don't want it partly working and partly wrong; don't want to be like 'poisoning the brand'.For everyone's knowledge, this is just a prototype of something I may pitch to HR.
Just because you're paranoid doesn't mean they're NOT out to get you.

 Posts: 288
 Joined: Thu Oct 24, 2013 12:32 am
Re: Excel Formula Cannot Figure Out
I hope you can go meta on this and realize why complicated Excel formulas are not that great for HR/Comp issues. The first issue is mistakes which you already made, the second is complexity. Your rules seem simple but as you start describing them it's not quite that simple after all since there are grant dates on Jan 1 and on certain threshold anniversaries, plus rollover limitations.Mr400meterdash wrote: ↑Thu Dec 19, 2019 3:17 pmI am trying to make an excel spreadsheet that can track PTO.
You are correct on me having my numbers incorrect. Sorry!
year 1end of 2 is 128
beginning of year 3 to end of year 7 is 168
beginning of year 8 to end of year 11 is 208
beginning of year 12 to forever is 248
If a person joins in October they get 3 months worth of PTO so 32 hours. Rollover is 5 days. PTO days accrue annually so we get all days Jan 1 of the next year.
The problem I am running into at the moment is having the formula understand that it cant change the numbers until the next year.
For example. Lets say the hire date was 122017. This year I should still have 208 hours of PTO but as soon as tomorrow hits, it will give me 40 more hours.
For everyone's knowledge, this is just a prototype of something I may pitch to HR. It is not a big company or anything like that but thought I could reduce costs for us by making something simpler. I also love playing around with excel so thought why not. I would create a database for this if it was going to be a true idea but I like making things in excel first.
Thanks!
You can still do it in Excel (maybe), but I would stage your formula into a few columns and lookups. You can always hide columns with intermediate values but if you need to check or verify the formulas it's much easier to have each stage in its own column.
Are you trying to make a tool that helps HR/Finance? A tool that helps your coworkers? Are you just trying to illustrate a new compensation policy you are proposing?

 Posts: 69
 Joined: Wed Dec 13, 2017 10:24 am
Re: Excel Formula Cannot Figure Out
I only thought of this today and was just playing around to see if I could come up with something better than what we currently have. I understand that there is a lot of complexity to this but was just starting with some of the basics. It is very much possible that I scratch this idea in a month or so if I cannot find a better solution.lotusflower wrote: ↑Thu Dec 19, 2019 3:37 pmI hope you can go meta on this and realize why complicated Excel formulas are not that great for HR/Comp issues. The first issue is mistakes which you already made, the second is complexity. Your rules seem simple but as you start describing them it's not quite that simple after all since there are grant dates on Jan 1 and on certain threshold anniversaries, plus rollover limitations.Mr400meterdash wrote: ↑Thu Dec 19, 2019 3:17 pmI am trying to make an excel spreadsheet that can track PTO.
You are correct on me having my numbers incorrect. Sorry!
year 1end of 2 is 128
beginning of year 3 to end of year 7 is 168
beginning of year 8 to end of year 11 is 208
beginning of year 12 to forever is 248
If a person joins in October they get 3 months worth of PTO so 32 hours. Rollover is 5 days. PTO days accrue annually so we get all days Jan 1 of the next year.
The problem I am running into at the moment is having the formula understand that it cant change the numbers until the next year.
For example. Lets say the hire date was 122017. This year I should still have 208 hours of PTO but as soon as tomorrow hits, it will give me 40 more hours.
For everyone's knowledge, this is just a prototype of something I may pitch to HR. It is not a big company or anything like that but thought I could reduce costs for us by making something simpler. I also love playing around with excel so thought why not. I would create a database for this if it was going to be a true idea but I like making things in excel first.
Thanks!
You can still do it in Excel (maybe), but I would stage your formula into a few columns and lookups. You can always hide columns with intermediate values but if you need to check or verify the formulas it's much easier to have each stage in its own column.
Are you trying to make a tool that helps HR/Finance? A tool that helps your coworkers? Are you just trying to illustrate a new compensation policy you are proposing?
My dad create databases for his job so I would learn from him on that and not have the final product be in excel but wanted to play around in excel first to see if this idea is even worth pursuing.
This took would only be for HR for PTO time. They spend what I consider too much money on a program to track PTO when the program has tons of features we will never use. It also may just be easier to look into purchasing something a lot cheaper to do this but I am hands on and want to see if I can create something first.

 Posts: 860
 Joined: Tue May 15, 2018 10:14 pm
Re: Excel Formula Cannot Figure Out
Details of the rules will also matter.
Examples:
Q1: if someone first started work Aug 15, 2017 how many PTO hours did they get in 2017? Was it only 32, ie for the complete 4th quarter and none for Aug 15 to Sep 30? Or is it prorated and if prorated, how finely, ie nearest hour of PTO, nearest quarter hour of PTO, nearest full day of PTO??? Is what matters the number of full quarters they will work that year, or the number of full months, or pay periods or days?
Next detail.
Q2: I get that in Year 1 and 2 they get 128 hours per year; but when exactly does Year 3 begin?
So back to our friend who if someone first started work Aug 15, 2017: does year 3 begin Aug 15, 2019? Or Jan 1, 2020. Or Jan 2, 2019? And if the increase from 128/year to 168/year happens on the anniversary date of their hire, how is the partial month, partial quarter dealt with  see Q1.
If all details can't be clearly defined in text, then they can't be accurately represented by a spreadsheet or computer program.
Examples:
Q1: if someone first started work Aug 15, 2017 how many PTO hours did they get in 2017? Was it only 32, ie for the complete 4th quarter and none for Aug 15 to Sep 30? Or is it prorated and if prorated, how finely, ie nearest hour of PTO, nearest quarter hour of PTO, nearest full day of PTO??? Is what matters the number of full quarters they will work that year, or the number of full months, or pay periods or days?
Next detail.
Q2: I get that in Year 1 and 2 they get 128 hours per year; but when exactly does Year 3 begin?
So back to our friend who if someone first started work Aug 15, 2017: does year 3 begin Aug 15, 2019? Or Jan 1, 2020. Or Jan 2, 2019? And if the increase from 128/year to 168/year happens on the anniversary date of their hire, how is the partial month, partial quarter dealt with  see Q1.
If all details can't be clearly defined in text, then they can't be accurately represented by a spreadsheet or computer program.
Just because you're paranoid doesn't mean they're NOT out to get you.
Re: Excel Formula Cannot Figure Out
I did not know this! This is great! Thanks, mlipps!mlipps wrote: ↑Thu Dec 19, 2019 12:21 pmYou don't need to nest the IF statement, you can instead use the function "IFS" to evaluate multiple if statements:
https://exceljet.net/excelfunctions/excelifsfunction
"Reverify our range to target ... one ping only."

 Posts: 288
 Joined: Thu Oct 24, 2013 12:32 am
Re: Excel Formula Cannot Figure Out
Well I fully support learning and prototyping and figuring out Excel and databases, and saving the boss some money. Just be aware that whatever package HR is using has all kinds of features like reporting and security and audits and employee records privacy etc. Whatever arithmetic is being done is not why they pay for the software... because they could just do the arithmetic part in an Excel spreadsheet.Mr400meterdash wrote: ↑Thu Dec 19, 2019 3:47 pmThis took would only be for HR for PTO time. They spend what I consider too much money on a program to track PTO when the program has tons of features we will never use. It also may just be easier to look into purchasing something a lot cheaper to do this but I am hands on and want to see if I can create something first.

 Posts: 860
 Joined: Tue May 15, 2018 10:14 pm
Re: Excel Formula Cannot Figure Out
I'm betting that a lot of people have trouble figuring out what the correct amount of PTO is. Maybe even the HR employees have a lot of trouble figuring it out. The HR software may be buggy. If any of these are true it might be because the rules are somewhat complicated an nonintuitive when comes down to specific details.
Just because you're paranoid doesn't mean they're NOT out to get you.
Re: Excel Formula Cannot Figure Out
The first step in doing anything like this is to gather the requirements for the software and make a data model of the problem. You have to represent all the rules that apply. It could well be that all the mathematical work you need can be done in Excel, but it is unlikely you will arrive at a practical tool that can be shown to be error free while taking into account all the elements of a correct model. As mentioned, at the very least, there is going to have to be a confidential database of employee data.Mr400meterdash wrote: ↑Thu Dec 19, 2019 3:47 pm
This took would only be for HR for PTO time. They spend what I consider too much money on a program to track PTO when the program has tons of features we will never use. It also may just be easier to look into purchasing something a lot cheaper to do this but I am hands on and want to see if I can create something first.
You may be right that they are paying for a capability that exceeds their needs, but it may be worth it or it may be worthwhile shopping for a different model. Is it really true that the only job HR has for this tool is computing PTO entitlement, presumably for the purpose of advising employees of their entitlement and perhaps deciding if requested time off can be authorized.

 Posts: 1895
 Joined: Sat Jan 20, 2018 4:02 pm
Re: Excel Formula Cannot Figure Out
I echo this sentiment. VLOOKUP is nice in that it stops at the last row that matches the criterion. Here's a snippet using the OP's example:lotusflower wrote: ↑Thu Dec 19, 2019 12:35 pmWish I worked at your company, 4380 hours is a lot of PTO!
You can nest logical IFs, but personally I think complicated Excel formulas are a big problem with Excel, and using a complicated formula for anything related to HR and comp puts a business at risk. Especially when you start copying formulas and it's easy to get relative vs absolute cell references wrong.
I would make a column that computes the number of years worked, and then I would make a table, maybe on another sheet, that has years of service in one column and PTO hours in the next column. Then you can use VLOOKUP in another column to pull the right pto value out of the table.
Code: Select all
Starting date: 12/18/2011
Today: 12/19/2019 =TODAY()
Years worked: 8.002777778
PTO: 208 =VLOOKUP(B3,A8:B11,2,TRUE)
Leave Lookup Table
Years PTO Hours
0 128
3 168
8 208
12 248
Re: Excel Formula Cannot Figure Out
OP, you received good answers, but next time, you may want to ask on a forum dedicated to Excel. I occasionally used this one with good results:
https://www.mrexcel.com/board/
https://www.mrexcel.com/board/