Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Consecutive Days - Yes or No??

Posted on 2011-05-09
13
Medium Priority
?
365 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Runrigger
  • 7
  • 6
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35720256
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35720278
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
 
LVL 11

Author Comment

by:Runrigger
ID: 35720328
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35720340
It needs to be array-entered, as I mentioned (with Ctrl+Shift+Enter).
0
 
LVL 11

Author Comment

by:Runrigger
ID: 35720419
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35720464
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
 
LVL 11

Author Comment

by:Runrigger
ID: 35720541
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35720583
You mean they should be counted as working days, unlike weekends?
0
 
LVL 11

Author Comment

by:Runrigger
ID: 35720595
Yes, they should Rory, sorry!

One of the other managers just pointed it out to me a short time ago.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35720635
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
 
LVL 11

Author Comment

by:Runrigger
ID: 35720859
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35720961
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
 
LVL 11

Author Closing Comment

by:Runrigger
ID: 35721055
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question