August 31, 2016
I need to create a formula that calculates the number of sick days an employee gets from hire date (1 day at 6 mos, 2 days at 1 yr, 3 days at 2 yrs, 4 days at 3+ yrs) based upon the current date at any given time [i.e. TODAY()]. I have attached a file and indicated the column where the formula is needed. Every thing I try seems to ignore the first 6 mos from hire date. Can you please help me? Thank you!!!
July 13, 2016
Hello CJ18,
I had a look and I cannot see a problem with the formula. If I go down to line 195 and change the date for Employee # 16ZAH so that they started in the last 3 months - their calculation for days to add at next anniversary shows 0.
If I amend that start date to show as 7 months ago, it changes to 1.
Is that not what the formula is supposed to do? Or are you wanting it to have the 1 in place prior to the anniversary date passing? (in which case all the "0" values in the formulas at the end would need to change to 1).
I also noted that the 'A type" employees after line 196 have a slightly different formula in the K column, such that all values are 4. Perhaps this is causing an issue?
Other than that, I was a little unclear on what the group of 3 columns was trying to achieve, and whether it was actually column M where you wanted the formula.
Cheers,
Adrian
VIP
April 21, 2015
Same conclusions and questions as Adrian has. It seems to work as intended. Maybe you can tell us which line doesn't give the 'right' number and what you expect there, so we understand what you think is going wrong?
Only thing I see is that D3 is formatted as date and the dates in columns D:H as 'special', but I don't think that's something that's bothering here.
And I'm not sure what you want with columns L and M? Is there where the problem is?
August 31, 2016
I marked at the top of the spreadsheet, Column M is the column where the formula is needed, but that was before I deleted the names. It should be Column L that needs the formula, my apologies. The 'A Type' employees are salaried and do have a different formula, so I am not concerned about them so much. Column K indicates the number of days the employee is to receive at their anniversary, but not until they reach their anniversary date. Column L is supposed to reflect what is available based upon the current date (i.e. D3). So, the difficulty I'm having is that when I get it to recognize what the employee should receive, then I can't seem to figure out how to get it to recognize that 'TODAY' may be before their anniversary date, so it should show 0. Thank you for your input and suggestions!
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
Neither of them have reached the anniversary date yet so they show 0 as what you have requested below.
"So, the difficulty I'm having is that when I get it to recognize what the employee should receive, then I can't seem to figure out how to get it to recognize that 'TODAY' may be before their anniversary date, so it should show 0."
VIP
Trusted Members
June 25, 2016
VIP
April 21, 2015
1 Guest(s)