Occurences of Dates in range

[spoursy]

Dates and the frequency in a rolling year

I am applying the provision of a sickness policy into a worksheet where every episode of sickness is recorded and that the first date of each period sickness is entered. As I am also applying historical data not all of the dates are in chronological order.
I can list all of a staff members dates by way of a dynamic list, that list will be in cells a5:a15
I need the spreadsheet to look for the most recent episode and then check the historical data to tell me in one cell if this most recent episode is:
The 1st episode in 6 months – “1st in 6”
The 2nd episode in 6 months – “2nd in 6”
The 3rd episode in 6 months – “3rd in 6”
I also need it to be able to tell me if there have been 4 episodes in a year (with 2 episodes falling within two separate 6 month periods) – “4th in 12”
Any ideas

[Glenn Kennedy]

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[spoursy]

excel q 2.xlsx
excel q 2.xlsx
Hi

Should be there now

[Glenn Kennedy]

They are there OK, but I still don't understand. Are all these absences relating to one person? Why do the dates jump backwards and forwards as you go down the column?

[spoursy]

Hi
Yes they relate to one person
Ther reason why the dates jump is that there is some historic data in the database which isnt in date order

Roger

[Glenn Kennedy]

What do you want to see when an absence is > 1 year ago. Blank, or what?
Do you want the formula to be based on today(), or something else?

[Glenn Kennedy]

Work in progress. Here's what I have so far...

=COUNTIF($A$5:A5,">"&TODAY()-365)&CHOOSE(AND(MOD(COUNTIF($A$5:A5,">"&TODAY()-365),100)<>{11,12,13})*MIN(4,MOD(COUNTIF($A$5:A5,">"&TODAY()-365),10))+1,"th","st","nd","rd","th")&" in 12"

B5 and copy down.

[spoursy]

Glenn

Looks good so far.
If it is more than a year ago a blank would be good
Id need to substitute today() to a cell which contains the date of the latest episode

R

[Glenn Kennedy]

Brrr. Should be OK. I think. I'll be back.

[spoursy]

Also Glenn...
The legent would need to incorprate
The 1st episode in 6 months – “1st in 6”
The 2nd episode in 6 months – “2nd in 6”
The 3rd episode in 6 months – “3rd in 6”

[Glenn Kennedy]

Try this. I think I may be overcomplicating things....

[spoursy]

I like complicated stuff!
I still need the stuff in post 10 which might overcomplicate it further

[Glenn Kennedy]

I didn't spot that post. What's the basis for 6 or 12... based on time back from latest event or what?

[spoursy]

Its basically this: (which is quite complicated!)

We refer to any single period of absence as an ‘episode’ of sickness. If you are off ill for a 2nd episode (period of absence) in a six month rolling period, you can expect that your Manager will meet with you to discuss their concerns about your levels of absence. If in the following 6 months if there are a further 2 episodes it will be necessary to progress directly to the 1st stage of this policy. If you are off ill from work for a 3rd period of absence (in 6 months), and there are no underlying relevant and identified medical issues, the formal part of the sickness absence procedure will commence at Stage 1 of the process.

All basis are from the most recent event

[Glenn Kennedy]

OK. Slight re-think... I think. Look back a bit later on oday. I'm away for a while.

[spoursy]

Many thanks

[Glenn Kennedy]

Try this. A bit ugly, but it works and is not resource hungry.

[Glenn Kennedy]

On second thoughts...

[spoursy]

That's brilliant so far. The 3rd episode is in the 6 months not the 12. So if there are 3 episodes in 6 months it would apply. Sorry to be pedantic.

[Glenn Kennedy]

I don't think we're there yet, but my silly goof has been corrected.

[spoursy]

Thanks so much for this. I cant work out how the formula works! Do you think getting the 3rd episode in 6 months is feasible?

[Glenn Kennedy]

Apologies. i forgot about you. Please remind me WHY the 3rd is in 6 months, not 12... It's more than 6 months since the first absence. Am i misunderstanding you here?

[spoursy]

It’s all to do with our very complicated sickness policy – Welcome to the NHS!

If you have 2 episodes of sickness in a 6 month period management should issue a letter of concern
If you have a 3rd episode in the same six month period you would progress to a stage 1 of the formal process, but this can only happen if there isn’t an underlying medical condition
If you have 4 periods of sickness in the year (with 2 episodes happening in a 6 month period and a further 2 in the following 6 months – but this essentially means 4 in 12) then we can progress to a stage 1 even if there is an underlying medical condition

[Glenn Kennedy]

OK. Apply the NHS equivalent of logic to these:

01/01/2015 1st in ???
01/04/2015 2nd in ???
01/08/2015 3rd in ???
01/09/2015 4th in ???
01/12/2015 5th in ???

[spoursy]

Hiya - welcome to NHS Logic

Row 16 is the reference date point

[Glenn Kennedy]

Regarding the formula,

=IF(A5<EDATE(MAX($A$5:$A$30),-12),"",RANK(A5,$A$5:$A$30,1))-COUNTIF($A$5:$A$30,"<"&EDATE(MAX($A$5:$A$30),-12))

is giving you the 1,2,3 for all episdodes in the 12 months prior to the most recent.

The lengthy middle bit is giving you the st nd rd, etc (and could be HUGELY simplifed if you were OK with using a UDF (macro enabled).

I'm still lost with what you need at the last bit. Does this follow the logic of what you want? It is hugely confusing...