WORKDAYS within range in specific month range

[RoundaboutCJP]

Totally stuck trying to find a way around this one.
Really appreciate any and all help.

Column A : Start date of my annual leave
Column B : End date of my annual leave
This then forms a list of periods of annual leave with a different period in each row.

Then I have a table on another sheet with the months in row 1 [Jan - Feb - Mar - Apr - May - etc..]

What I want is a formula that tells me how many days off I had each month. This would sit in each cell in row 2.

I've looked at using NETWORKDAYS.

BUT The issue I have is that an instance of annual leave might span a month end. i.e. 25/1/17 - 08/2/17
This would need to report 5 days leave in Jan and 6 days leave in Feb with 4 days removed from the calculation because they were weekends.

Pulling my hair out!

Thanks

[kasan]

Hi and welcome!
Main issue of using Networkday() is Holiday days.
I would suggest to make a list of all dates of the year (write 01/01/2016 in cell A1 and pull down till 12/31/2016)
Then in column B write formula and pull it down:

Please Login or Register  to view this content.

Now you have all dates of all holidays of the year.
Then enter Start date, End date and as Holiday parameter of NETWORKDAYS() - use all dates in column B.
Example attached.

[RoundaboutCJP]

Hi,

Thanks for the welcome and for the help and advice.

I think I understand what you've done, but I'm not sure I fully get what Column B is doing.
Surely =NETWORKDAYS(D4,E4) would give the same result? What is the holiday column achieving?

I also don't think it's kicking out the result I am looking for as the result you produce is 11.
What I want is a split result - Jan = 5 and Feb = 6
I've updated the example and attached, hopefully this makes it clearer?

Thanks so much

[kasan]

As option you can use function EOMONTH(), this one return the last day of the month.
My idea is to check if last day of the Start month is equal to last day of the End date (for 01/01/2016 and 05/01/2016 - end of month will be the same = 01/31/2016 which means we can calculate NETWORKDAYS() and be sure they all was in January, Days_1 will be 3 and Days_2 = 0 (because there were no leaves in different month). In case Start Date is 01/25/2016 and End Date is 02/08/2016 - Days_1 will be calculated from 01/25/2016 till 01/31/2016 and Days_2 will be calculated from 02/01/2016 till 02/08/2016.)
Them summary table calculates all January days, all February days and so on.
Hope I got it right this time

[soledad]

Here is another way

[FlameRetired]

@ soledad,

Nice one.

Thanks for the lesson.

Dave

[RoundaboutCJP]

Hi Kasan,

Looks good. I like the solution and it makes sense, plus it works when you play with it in order to grow and expand it.

2 further thoughts - just to up the stakes!

1) Is it possible to add a 3rd month? i.e. What if the leave period began on 20/1/16 and ended on 3/3/16. (See yellow highlighted line in the attached). Would it be expandable to account for that?

2) What if the list of leave dates related to more than one employee. Lets say a column before the date includes the employee name and we want to separate out leave taken, not only by month, but also by employee. Again see attached. Can this IF function be added to the table that does the reporting?

Any thoughts on expanding it this way? I've had a stab, but failed!

Cheers.

PS I went to Riga once, for 12 hours on a flight stopover - nice city (from what I saw of it!)

[kasan]

Hi,
1) it is possible, but as you can see in attached file - formulas becomes more complicated. Formulas now can calculate days for three months maximum. For 4 months this will not work.
Now logic of calculations is:
Days_1 = count of days in first month.
Days_2 = here is a count of days of full month only (in case full month is within Start_date and End _date), otherwise = 0
Days_3 = count of days of last month.
2) it is possible too, I think that better way to manage this is SUMIFS() function, because it can sum numbers on several criteria. Difference from calculating Total count of the days - we just add one more criteria - employee name.
Check out attached file, hope this helps

[FlameRetired]

Hi Kasan,

Looks good. I like the solution and it makes sense, plus it works when you play with it in order to grow and expand it.

2 further thoughts - just to up the stakes!

1) Is it possible to add a 3rd month? i.e. What if the leave period began on 20/1/16 and ended on 3/3/16. (See yellow highlighted line in the attached). Would it be expandable to account for that?

2) What if the list of leave dates related to more than one employee. Lets say a column before the date includes the employee name and we want to separate out leave taken, not only by month, but also by employee. Again see attached. Can this IF function be added to the table that does the reporting?

Any thoughts on expanding it this way? I've had a stab, but failed!

Cheers.

PS I went to Riga once, for 12 hours on a flight stopover - nice city (from what I saw of it!)

RoundaboutCJP,

Have you examined soledad's formula. It lends itself to both thoughts.


This is a variation on that formula applied in the attached. It has to be array entered in O8 then filled across and down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[soledad]

@FlameRetired
Thank you for your reply
I think it could be shorter as below

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER

[FlameRetired]

@ soledad

Yup. Another good one.

[RoundaboutCJP]

@Kasan Thank you - looks great, does the job and makes sense (I understand what it is doing) and it fits the bill. Thank you.
@FlameRetired - thank you. I did look at the other version but had zero knowledge of array formula so couldn't work out how to make that solution work. You have expanded my function knowledge - thank you
@soledad Thank you for another good solution that stretched my Excel understanding. Now I know it is using array formulas I think I get it. Its a bit trickier for a newbie to get his head about what its doing but I am playing with it and think I get it. Thanks for the updated solution too.
Cheers all, massively supportive and helpful

[FlameRetired]

Thank you for the feedback.

[RoundaboutCJP]

Just managed to put it all into play.

One observation when applying it to a real life scenario would be:
In an ideal world you would hide the columns Days_1, Days_2 and Days_3. BUT sometimes leave might be 1/2 a day rather than a whole day.
The only way to account for this as far I can see is to manually edit the info in those 3 columns to input a '.5' figure.

Its not clean but it functions!

[RoundaboutCJP]

@Kasan One further confusion. I assumed that if I were to change the spreadsheet to look at 2017 leave, not 2016, then I would need to change the list of dates in cols A and B.
But replace all '2016' with '2017' in those columns simply causes an error.
Q) What do cols A and B do? What is their function?
Q) What is the correct way to alter the spreadsheet to work with 2017 dates
Q) What would need to be done for the spreadsheet to have a list of dates that covered both 2016 and 2017 dates, all in one list?

Thanks so much. Really enjoying playing with this and following the advice you are giving.