Sumifs in vacation tracker, need help

[Maja]

Hi,

I try to find out how to make sumifs formula in vacation tracker when the end date of vacation is in another month. When start date is 11.01.2014. and end date is 03.03.2014 and I want to calculate how many days employee was on vacation just in january. Because vacation starts in current month and it ends on next month formula return no value =SUMIFS(Vacations['# of days taken];Vacations[Start Date];">="&B12;Vacations[End Date];"<="&EOMONTH(B12;0);Vacations[Vacation Code];"<>"&3) . In cell B12 is 01.01.2014. How to fix that?
I attach file so you can see my problem.

Thanks,
Maja

 

[NARAYANK991]

Hi Maja ,

Will there be multiple entries with a vacation code 1 , 2 so that for each month many entries will have to be summed up ?

Narayan

 

[Maja]

Yes, thats right. For 20 employess and for every vacation type that is on the list.

 

[NARAYANK991]

Hi ,

In that case can you upload a working file rather than a sample file ? I assume your data layout will be different from what your uploaded file shows.

Show at least the expected data layout for the output.

Narayan

 

[Maja]

Dear Narayan,

I upload my file and hope you can help me.

Maja

 

[NARAYANK991]

Hi Maja ,

Thank you for uploading your working file.

I have a doubt ; you say that the availability of agents in January 2014 is 15 ; however , out of the total number of 15 agents , the data shows :

1. John is on leave for the whole of January
2. Kevin joins only in April
3. Steve and Leona join only in September

Thus , in January , the availability would be 14 ; is this correct ?

Narayan

 

[Maja]

Yes it's correct, and thats what I want to calculate. Head count for january is 15, but we have one person on vacation whole month, so available agents is 14. Acctualy, I want to make a formula that shows result of 93,33% availability of agents in january, than for february...on so on... If employee is missing for some reason, percent shoud change automatically to correspond to that. And when someone stop or start working in any month percent will change as well.

 

[Faseeh]

Sorry for interruption Guys,

With the old sample file i reached this one:

=SUM(IF($D$5:$D$7>=B12,IF($D$5:$D$7<=EOMONTH(B12,0),IF($E$5:$E$7<=EOMONTH(B12,0),IF($G$5:$G$7=D$11,$E$5:$E$7,EOMONTH(B12,0))))))-SUM(IF($D$5:$D$7>=B12,IF($D$5:$D$7<=EOMONTH(B12,0),IF($G$5:$G$7=D$11,$D$5:$D$7,FALSE))))

with CSE and D11 contains 1,2,3 etc.

[@Maja, since i was working and not able to reach a solution so i have posted it now, you can continue with Narayan on your conversation., Thanks]

 

[NARAYANK991]

Hi Maja ,

Since Faseeh has come up with a formula , can you try it out in your latest uploaded file and confirm whether it gives you the required output ?

Narayan

 

[Maja]

Hi Guys,

I put that formula in my old sample file, that Faseeh used for formula, but it doesnt gives me good output. First I need sum of network days, second, when I change end date formula go bananas I attach file.

As I wrote in my last post, i acctualy need to calculate agents availabilty by month. We know how many people are on payroll list, and we want to find out, based on employee absent period, how many are available to do work. With that output, we can then calculate our target and KPI.

Thanks for helping me...

Maja

 

[Faseeh]

Hi Maja,

I interrupted the conversation and also provide formula that goes bananas!!

Please see attachment -Sheet Name: Rework. Hope that helps.

 

[Maja]

But, how can I just show number of working days, from monday-friday and exclude weekday from Total number of days in your formula? Can I change your formula with structured references and use CSE?

Thanks for the trouble...

 

[Faseeh]

Let me try...

 

[Faseeh]

Please see attachment, my apologies if it again go bananas...

 

[Maja]

Faseeh,

Thanks!

 

[Faseeh]

Maja,

Here is another option, i think this is much better. Please see

 

[Maja]

Yes, its much better! Thanks