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

**MDBaker85** · 10-25-2011, 09:04 AM

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.

**kenkie** · 10-25-2011, 09:16 AM

hi sir,

this is just a quick suggestion (cause i'm in a middle of working on my report when I saw your post...Ü).

**MDBaker85** · 10-25-2011, 09:25 AM

Hi kenkie,

Many thanks for your quick reply.

Unfortunately I changed the 2nd code as suggested, but I still have the same problem. Tried entereing 'h' or 's' into the cells from 26/12/2011 onwards, and it was still pushing the qualifying date back.

**Andrew-R** · 10-25-2011, 09:38 AM

This is a bit of a tricky one, isn't it?

I'd say that you need a macro solution, if that would be acceptable. However, your diary doesn't have a year reference, which is going to complicate matters.

Are you happy to do this with a macro, because I can't see any way that you can do it with standard formula in any sort of manageable way.

**MDBaker85** · 10-25-2011, 09:48 AM

Hi Andrew-R,

Thanks for taking the time out to reply to me.

I dont have much experience with macros. The only time I have used them was to create a button and assign a macro to redirect to another sheet within the workbook. I am always looking for new things to learn though so would be open to suggestions.

Also, if you think adding a year reference somewhere in the sheet would help, by all means do so. The only thing I really need to keep the same is the range of cells (B6:AF10). Dates etc, can be placed elsewhere in the sheet if needed.

**Andrew-R** · 10-25-2011, 09:59 AM

How about this worksheet function ...

Function IncrementEndDate(rngCalRange As Range, ByVal dQualDate As Date, ByVal dStartDate As Date) As Date

Dim rngCellLoop As Range
Dim dTmpReturn As Date
Dim dCurrDate As Date

dTmpReturn = dQualDate
dCurrDate = dStartDate

For Each rngCellLoop In rngCalRange.Cells
  If dCurrDate <= dTmpReturn And rngCellLoop.Value <> "" Then
    dTmpReturn = dTmpReturn + 1
  End If
  dCurrDate = dCurrDate + 1
Next rngCellLoop

IncrementEndDate = dTmpReturn

End Function

You can insert this straight into your existing VBA module and lets you use =IncrementEndDate as a worksheet function.

The parameters are:

=INCREMENTENDDATE(<Range of calendar>,<Base qualifying date>,<Calendar start date>)

So =INCREMENTENDDATE(B17:AF21,"27/12/2011","01/10/2011") gives me the correct result.

**daddylonglegs** · 10-25-2011, 10:14 AM

Here's a formula solution.

I changed the dates in column A so they are actual dates - each one should show the 1st of the month, e.g. 1-Oct-2011......but that is formatted as "mmm" to show as "Oct" etc.

Also I made 31-Nov black like 30 + 31 Feb (because that isn't a valid date).

For my solution to work these three cells should also be filled with something (I used "x")

Assuming that you only enter "h" and "s" in holiday and sick days this formula should be sufficient for counting absent days

=SUM(COUNTIF(B6:AF10,{"h","s"}))

and you can use this "array formula" for the qualifying date

=SMALL(IF(B6:AF10="",IF(B5:AF5+A6:A10-1>DATE(2011,10,3),B5:AF5+A6:A10-1)),84)

confirmed with CTRL+SHIFT+ENTER

See attached

**MDBaker85** · 10-25-2011, 10:16 AM

Hi again,

That is an extremely helpful response

However, due to my own ignorance and inexperience, I dont know how to insert that into a VBA module? Are you able to explain how I can do this, or point me in the direction where I can find out?

Once again, I really appreciate your time.

**Andrew-R** · 10-25-2011, 10:29 AM

Unless you would rather go with DLL formula solution then:

1. With your workbook open press Alt-F11 to open the VB editor
2. In the left hand pane double-click on Module1
3. Scroll down to the bottom
4 Paste in the code I posted above
5. Close the VB editor

Job done.

**MDBaker85** · 10-25-2011, 10:33 AM

EDIT:

daddylonglegs,

Seriously, i'm not even sure how you figured that out, but that does exactly what I need, thank you so so much. I should've come on here days ago.

Andrew-R,

Thank you millions for that, that also does exactly what I needed.

I don't think I have ever come across a more helpful community on the internet.

I'm spoilt for choice now!!

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions