Consecutive Days - Yes or No??

All, attached is a spreadsheet I have put together for my immediate manager in order for him to help manage his whole team's annual leave requirements.

One of our company's rules is that everybody is required to take a least 10 consecutive working days once per year.

My leave is detailed in column H and you will be able to see that I have 10 consecutive working days booked during the month of August.

What I would like add is a flag at the top of the sheet advising as follows;

No - the person has not yet booked 10 consecutive days
Booked - Yes the person has booked, but not yet taken
Yes - Yes booked and yes has taken

The sheet has a number of formula on already, it also has a number of conditional formatting, I do not mind adding as much formula as needed to work this out!

Would love to grant more than 500 points on this!
Holiday-Sheet---BAU.xls
LVL 11
RunriggerAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Revised formula:
=MAX(FREQUENCY(IF(AO$9:AO$395=1,ROW(AO$9:AO$395),IF($F$9:$F$395="*",ROW(AO$9:AO$395))),IF(ISNUMBER(SEARCH("WD",$E$9:$E$395)),IF(AO$9:AO$395<>1,ROW(AO$9:AO$395)))))

It's actually 11 for that col as the Friday before (row 126) is a BH)
0
 
Rory ArchibaldCommented:
The highest number of consecutive workdays I think can be handled with:
=MAX(FREQUENCY(IF(ISNUMBER(SEARCH("WD",$E$8:$E$394)),IF(G$8:G$394=1,ROW(G$8:G$394))),IF(ISNUMBER(SEARCH("WD",$E$8:$E$394)),IF(G$8:G$394<>1,ROW(G$8:G$394)))))
array entered. Barry can doubtless do it a lot shorter. :)

0
 
Rory ArchibaldCommented:
I think that can be shortened to:
=MAX(FREQUENCY(IF(G$8:G$394=1,ROW(G$8:G$394)),IF(ISNUMBER(SEARCH("WD",$E$8:$E$394)),IF(G$8:G$394<>1,ROW(G$8:G$394)))))
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
RunriggerAuthor Commented:
thanks Rory, but I get a #VALUE! if I enter either of these in cell G1

I am working in 2003 BTW, might make a difference and I should have stipulated that, sorry

0
 
Rory ArchibaldCommented:
It needs to be array-entered, as I mentioned (with Ctrl+Shift+Enter).
0
 
RunriggerAuthor Commented:
I am a twerp, should have realised that, that works a treat.

That's part of the answer, the other part asked whether it could be determined if the 10th day was in the past of the future?

If this second part is impossible then don't worry, I can work with the above.

Thanks Rory
Dave
0
 
Rory ArchibaldCommented:
I doubt it's impossible - I hadn't actually noticed that requirement. ;) Will try and figure it out shortly as I'm in the middle of something at the mo.
0
 
RunriggerAuthor Commented:
actually Rory, there was somethign else that I missed!!!!

Column F holds the value "*" to signify bank holidays, they would need to be treated as part of the consecutive days if they fall within to holiday booking!

I guess that I am starting to ask the impossible now!
0
 
Rory ArchibaldCommented:
You mean they should be counted as working days, unlike weekends?
0
 
RunriggerAuthor Commented:
Yes, they should Rory, sorry!

One of the other managers just pointed it out to me a short time ago.
0
 
Rory ArchibaldCommented:
So if you have a bank holiday followed by 4 days off , but no time off before the bank holiday, does it still count, or does it have to be within the booked holidays?
0
 
RunriggerAuthor Commented:
Rory, I think it was Barry Houdini in his EE blog that advised participants to write a "clear" question!

I should have clearly excluded the "within" @35720541 and simply stated that Bank Holidays should be treated as a holiday whether before, within, or after taking annual leave, provided that everything is consecutive.

In the original file attached, take a look at column AO, rows 129 - 142

Mon = BH, T/W/Y = Annual Leave, Fri/Mon = BH, Tues-Fri = Annual Leave, that person should be considered as having taken 10 consecutive working days off.

I hope this is now clear, thank you for your patience.
0
 
RunriggerAuthor Commented:
ruddy marvelous, thank you Rory

You are correct, it is 11 as the previous Friday was also a BH.

I am more than happy with this, i can work out the date thing later!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.