• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Holidays Taken between certain dates.

K

Karen Menzie

New Member
Hello Again, still on my staff holiday chart - new headache!


I can find examples which apply to numbers but not text.

I'm looking to count the amount of "H" entries per employee's row, between certain dates.

I have a holiday chart, with 2 columns per day as staff can book a half day off or a full day.

An "H" means a half day holiday (am or pm depending on it's placement in the left or right column) and HH means a full day off.

I've posted a simplified version with just a few staff, and just 3 months (Jan-March 2015)

My holiday display chart is on sheet 2, on sheet 1 I have the summary. It's the summary page where I am looking to enter the formula to summarise the "H"s on sheet 2, per employee, between certain dates (past up to today and future).
I am looking for formulas for my columns F and G on sheet 1.

In my example book:
My columns run seamlessly from Jan 1 (colC) to Dec 31 (colFZ) for 2015, on sheet 2.
And my [made-up] names run down column A, from row 12 to 21


I can total all the holidays booked (thanks to previous help) but my boss has asked me to further show which are past and which are future.
(Because currently, in January, we look at the summary and it only shows the total of holidays booked, a total which will of course include future holidays - so it isn't immediately clear if a person has already used all 23 holidays already or just booked them in for the year ahead).

I know the today's date formula "=today()" , so I've entered that in a cell so that it can be used in a formula and show up as a reference point when printed - and that I will need some sort of greater than and lesser than formula.

My current formula in column E, which shows the total of holidays booked so far for 2015, (per employee row) is:
=COUNTIF(INDEX(Sheet2!$C$12:$FZ$21,MATCH($B6,$B$6:$B$15,0),),"H")/2

Which is copied down column E.

nb: H is divided by 2, as two Hs in one day is equal to one full day off.

Any help much appreciated!

Thanks in advance
 

Attachments

Thank you both so much. I'm blown away that you will give your time to help a me out so readily, I really appreciate it, I hope to pay it forward once I get better at excel myself!

Somendra that is perfect thank you so much you have saved me HOURS! Column F is exactly what I was hoping to achieve.

I realise now I wasn't clear enough with column G - I meant for it to show which of the booked holidays (in E) were yet to be taken, rather than how many were left to be booked at all, a simple G= E-F was all G needed - I realise now (sorry for not being clearer).

So so happy, thanks again!

Karen
 

Attachments

Hi All,

An alternate Solution using FLOOR()

Code: 
=SUMPRODUCT((A6&B6='S2'!A$12:A$21&'S2'!B$12:B$21)*(('S2'!C$9+FLOOR((COLUMN('S2'!B$11:FY$11))/2,1))>='S2'!M$2)*(('S2'!C$9+FLOOR((COLUMN('S2'!B$11:FY$11))/2,1))<=F$3)*('S2'!$C12:$FZ12="H")*1)/2

Put in F6 in Sheet1 (S1) and drag down.
 
You must log in or register to reply here.
Back
Top