Reset IF statement

[Digitelpersona]

Hi,

Our HR department has released a new policy for our employees that states if no sick days are used within a 90 day period they receive one personal day to be used within 30 days.

So I created an excel spreadsheet to keep track of this, but I’m running into some trouble with my IF statement. It currently reads: =IF(SUM(R23:R42)>0,"Not eligible",A23+90)

It works ok until someone is determined eligible and calculates the date, however I'm not sure how to get it to reset after that. Another words, it needs to start the evaluation process all over again.

I'm not sure if I've described my issue adequately enough, but if I have and someone can offer a suggestion I'd greatly appreciate it.

Thanks in advance,
Joe

 

[DPlank]

Can you provide an example of the data?

It looks like the it checks range R23 to R42 and if it finds a value >0, then the person is not eligble for the personal day. If not, it takes A23 (what date is this?) and adds 90 on to it.

I'm guessing, but is A23 the date of last sickness, and the A23+90 is supposed to give you the next 90 day perios to check if the person is eligible or not?

Need some clarification, please

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[Digitelpersona]

Yes R23 to R42 checks to see whether someone has used any sick time. If they have, the value would be greater than 0 and they would get "not eligible"

A23 contains the week ending date. This will change from week to week. A24, A25, etc.

Is it possible to attach a file on here? It would be so much easier if you could see the file I'm working with.

Thanks,
Joe

 

[DPlank]

It isn't possible to attach a file, but you can type in some data that is representative of your file.

That would be a start for us to help you out.

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[Digitelpersona]

Ok thanks.

Here is an example:


WEEK ENDING SICK USED BONUS DAY
1/7/2005 0 Not Eligible
1/14/2005 0 Not Eligible
1/21/2005 0 Not Eligible
1/28/2005 0 Not Eligible
TOTAL JAN 0 Not Eligible
2/4/2005 0 Not Eligible
2/11/2005 0 Not Eligible
2/18/2005 0 Not Eligible
2/25/2005 0 Not Eligible
TOTAL FEB 0 Not Eligible
TOTAL JAN-FEB 0 Not Eligible
3/4/2005 0 Not Eligible
3/11/2005 0 Not Eligible
3/18/2005 0 Not Eligible
3/25/2005 0 Not Eligible
TOTAL MAR 0 Not Eligible
TOTAL JAN-MAR 0 Not Eligible
4/1/2005 0 3/31/2005
4/8/2005 0 Not Eligible
4/15/2005 0 Not Eligible
4/22/2005 0 Not Eligible
4/29/2005 0 Not Eligible
TOTAL APR 0 Not Eligible
TOTAL JAN-APR 0 Not Eligible


This is what the Bonus Day Column should look like, at this point I can get it to work up to the point where the formula produces 3/31/2005, but subsequent to that it needs to revert back to Not Eligible, but I can't figure out how to do that using the following formula: =IF(SUM(R23:R42)>0,"Not eligible",A23+90)

I hope the above helps a little bit more!
Joe

 

[DPlank]

I can't tell from what you've given me datawise which cell/row is which. Also the range R23 to R42 is 19 cells, and according to your data above, there are 24 cells.

Am I correct in assuming the totals are not included in the count of column R?



Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[Gavona]

I haven't tried to fully understand your formula but think I understand the principle.

Instead of:
=IF(SUM(R23:R42)>0,"Not eligible",A23+90)

try along these lines:
=IF(SUM(R23:s42)>0,False,A23+90)
or
= if(or(sum(r23:s42),false,S23+90
If I have followed correctly Column r will contain the date when they were last eligible (or will next be eligible if no more sick days) by refering to that column rather than your week commencing dates you will count exactly 90 days.

You could also consider:
= Max(startDateOfPolicy,R$23:R9999)+90 in a single cell, formated as a date. It's a lot simpler and shows the date of next eligibility. Maybe you need something more visual though?

Thanks,

Gavin

 

[DPlank]

Gavona
The first formula you posted is no different functionally to the one being questioned (except you've changed the range being looked at from R23:R42 to R23:S42.

The syntax of = if(or(sum(r23:s42),false,S23+90
doesn't make sense and it won't work.

The OR function needs at least two items, otherwise there is no point. Depending on where the missing brackets go, you would get different answers from this statement.

The = Max(startDateOfPolicy,R$23:R9999)+90 looks like a good start, I'm assuming your startDateOfPolicy is a named range referring to the week ending dates?

The problem you have here is that Max will take the largest value from the list i.e. the last date and add 90 to that.

DigitelPersona
If you can answer the questions I've asked above, I can help further.

Best of luck



Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[Digitelpersona]

Hi Dave,

I'm sorry about not being clear about the cells/rows. Let me try this:

A B C
1 WEEK ENDING SICK USED BONUS DAY
2 1/7/2005 0 Not Eligible
3 1/14/2005 0 Not Eligible
4 1/21/2005 0 Not Eligible
5 1/28/2005 0 Not Eligible
6 TOTAL JAN 0 Not Eligible
7 2/4/2005 0 Not Eligible
8 2/11/2005 0 Not Eligible
9 2/18/2005 0 Not Eligible
10 2/25/2005 0 Not Eligible
11 TOTAL FEB 0 Not Eligible
12 TOTAL JAN-FEB 0 Not Eligible
13 3/4/2005 0 Not Eligible
14 3/11/2005 0 Not Eligible
15 3/18/2005 0 Not Eligible
16 3/25/2005 0 Not Eligible
17 TOTAL MAR 0 Not Eligible
18 TOTAL JAN-MAR 0 Not Eligible
19 4/1/2005 0 3/31/2005
20 4/8/2005 0 Not Eligible
21 4/15/2005 0 Not Eligible
22 4/22/2005 0 Not Eligible
23 4/29/2005 0 Not Eligible
24 TOTAL APR 0 Not Eligible
25 TOTAL JAN-APR 0 Not Eligible


So with the above data, my current formula would look like this: =IF(SUM(B2:B25)>0,"Not eligible",A2+90) and this would change for the next cell to look like this: =IF(SUM(B2:B25)>0,"Not eligible",A3+90)... etc.

And you are correct to assume that the totals don't have to be included.

Again, thank you for taking the time to help me with this.

Joe

 

[shetlandbob]

IF(SUM(B$2:B$25)>0,"Not eligible",DATE(YEAR(A2),MONTH(A2),DAY(A2)+90))

would this do what your after? (Not 100% clear what your after?).

This produces the following:

WEEK ENDING  SICK USED 	  BONUS DAY
7-Jan-05        0       Not eligible
14-Jan-2005     0       Not eligible
21-Jan-05       0       Not eligible
28-Jan-2005     0       Not eligible
4-Feb-05        1       Not eligible
11-Feb-2005     0       12-May-2005
18-Feb-05       0       19-May-2005
25-Feb-2005     0       26-May-2005
4-Mar-05        0       2-Jun-2005
11-Mar-2005     0       9-Jun-2005
18-Mar-05       0       16-Jun-2005
25-Mar-2005     0       23-Jun-2005
1-Apr-05        0       30-Jun-2005
8-Apr-2005      0       7-Jul-2005
15-Apr-05       0       14-Jul-2005
22-Apr-2005     0       21-Jul-2005
29-Apr-05       0       28-Jul-2005
6-May-2005      0       4-Aug-2005
13-May-05       0       11-Aug-2005
20-May-2005     0       18-Aug-2005
27-May-05       0       25-Aug-2005
3-Jun-2005      0       1-Sep-2005
10-Jun-05       0       8-Sep-2005
17-Jun-2005     0       15-Sep-2005

which shows when the eligible next day is? you might have to play around with the format statements of your cells (dates, the above was done using uk format dates)

Good luck!!

 

[Digitelpersona]

Yes, the only exception is that once someone is determined eligible, the formula need to reset back to "not eligible" until the next 90 days.

 

[shetlandbob]

hmm, I've ddeleted my little test sheet! Ok firstly get rid of the $ in the formula above!

Secondly, you'll need to add in a look back over the last 90days/weeks section of the formula to check whether a personal day has been allocated (should be able to do it the same way as above but looking into the past!)

 

[Digitelpersona]

Shetlandbob,

Thanks for the suggestion, but I'm not really sure how to create a "look back over" section or how to modify the current formula to do such.

Joe

 

[shetlandbob]

The tricky part to it is looking past the beginning of your dates.

Add in a column (C) between the sick days and the bonus day

put

if ( d2="Not Applicable",0,1) [red]  in cell c2 [/red]

in as your formula, this will create a 1 or 0 depending on whether the user was eligible for a bonus day or not that week.

Then modify your main code to search over the sum of the last 90 days (similar to your sum equation above), if the last ninety days has a 1 (i.e. bonus day allocated) then they are not eligible for another i.e.

=IF(SUM(B24:B47)+SUM(C14:C23)>0,"Not eligible",DATE(YEAR(A22),MONTH(A22),DAY(A22)+90))  [red]: e.g in cell d24[/red]

Be careful at the first few weeks of your equation (i.e. in the first cells (d2-d10)), as the second part of the sum is looking over cells above, i.e. into the past weeks