Register
Log In
Unanswered
Home
August 31, 2016
Offline
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!!!
Members
July 13, 2016
Offline
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
Dashboards
Power BI
Power Query
April 21, 2015
Offline
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
Offline
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!
Dashboards
June 25, 2016
Offline
Is column M as per your file your expected answer?
Dashboards
June 25, 2016
Offline
Try this in column L
=IF(OR(E7>$D$3,I7>=$D$3),0,K7)
Dashboards
June 25, 2016
Offline
Sorry change to
=IF(OR(E7>$D$3,I7>$D$3),0,K7)
August 31, 2016
Offline
Thanks SunnyKow, but I'm still not getting a 1 for the employees who have reached their 6 mos. That continues to be my issue. I did take the suggestion of Frans Visser and change the columns to dates from custom.
Dashboards
June 25, 2016
Offline
Hi
Just let us know which cells are not correct and what are the expected results. It is very difficult for us to make a guess.
August 31, 2016
Offline
Hi,
For instance, when I insert the suggested formula of: =IF(OR(E7>$D$3,I7>$D$3),0,K7) into Column L, the following employees [02JWC, 02RR, 02SNS, 03CML] are showing "0" and I need them to show "1."
Dashboards
June 25, 2016
Offline
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."
August 31, 2016
Offline
But they have reached their 6 mos anniv and therefore should have 1 day. That is what is making this so difficult. It is only difficult for the new employees, as the company wants to give them something before they reach their actual 1 yr anniv.
Dashboards
June 25, 2016
Offline
Not pretty but should work
=IF(AND(E7>=D3,K7=1),1,IF(OR(E7>$D$3,I7>$D$3),0,K7))
August 31, 2016
Offline
Are you getting it to work, because I am still getting "0" for the employees with 6 mos?
Dashboards
Power BI
Power Query
April 21, 2015
Offline
I think I understand the problem now. I'll come back later today after trying solving it, OK?
August 31, 2016
Offline
Thank you so much, SunnyKow!!! The only changes I needed to make to your formula was that the first > should be a < and to lock the $D$3 and now it works:
=IF(AND(E7<=$D$3,K7=1),1,IF(OR(E7>$D$3,I7>$D$3),0,K7))
Many thanks to all!!
Most Users Ever Online: 57
Currently Online: Max Neumann
21 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
