Formula to ignore data entered after the current projected qualifying date.

[MDBaker85]

Hi all,

This is my first post on the forums, so I apologise in advance for any mistakes when trying to describe the problem I have. I will try to be as thorough as I can.

If possible, I am looking for a formula that will ignore any data entered after the current projected qualifying date. I say current because the projected date could change based on information entered before it. Before I try to go into more detail, here is some useful information to take into account;

> The start date is 03/10/2011
> The member of staff needs to complete 12 weeks (84 calendar days) to qualify.
> Earliest qualifying date is 26/12/2011
> Sick days and annual leave will ‘pause’ the qualifying period.

I have attached an example workbook so you can see the layout and how I will enter the data. Unfortunately, the calendar must stay in the same format.

So, each day of the month is represented by a different cell.

If the cell is blank, this counts towards the qualifying period. If ‘h’ or‘s’ (holiday or sick) is entered, the qualifying period is paused for 1 day.

Now the problem is, as mentioned before, I need this formula to ignore anything after the projected qualifying date, but bare in mind this date could change based on the data that is entered before it. The reason for this is because as shown in example 1 on the spreadsheet, the member of staff has had no sick days/holidays so my current formula shows a projected qualifying date of 26/12/2011, which I know is correct.

However, in example 2 the member of staff, as per example 1, also has no sick days/holidays between 03/10/2011 and 25/12/2011, so the qualifying date should also be 26/12/2011. As you can see though, they are going on holiday the week after 01/01/2012. This should not affect the qualifying date as it has already passed, but my current formula pushes that date back by 5 days to 31/12/2011, which I don’t want it to. It should have stopped on 26/12/2011.

This is the current formula I use, entered as an array formula (Ctrl+shft+Ent):

=SUM(LEN(B6:AF10)-LEN(SUBSTITUTE(B6:AF10,"h",""))+(LEN(B6:AF10)-LEN(SUBSTITUTE(B6:AF10,"s",""))))

This will bring back the total number of holidays and sick days. In the adjacent cell my formula is:

=”26/12/2011”+AI7.

As I know for certain the earliest qualifying date is 26/12/2011, all I need to do is add the sum of the first formula onto that date for my current projected date.

So to summarise, using the above formula, or a completely new one, I need something that will not count data after the current projected date. I thought that maybe I could use a couple of different formulas, as I’m not sure if you can reference the same cell in a formula, so if you think it needs to be broken down some more, that is fine. However the fewer cells used, the better.

I hope this makes sense to someone out there.

I am really grateful to anyone for even taking the time to attempt to come up with a solution. I am not very experience with excel so am stuck for ideas now.

Many thanks.