Counting Days in a Month in a Date Range

[Leigh Digons]

I would like to count the days in the office in a given month, where there is a start date, Leave of Absence date (up to 3-4 leaves of absences), and a termination date.

I have attached the file. In Column P I have the formula:

=IF(OR($C8>Q$6,$C8=""),(((MAX(0,MIN(EOMONTH(Q$6,0),$I8)-MAX(Q$6,$H8)))+(IF(AND($B8<DATE(2015,2,1),$B8>DATE(2014,12,31)),($B8-Q$6),0)+(IF(OR($C8="",$C8>DATE(2015,1,31),$C8<DATE(2015,1,1)),0,(R$6-$C8)))))),0)


This is unfortunately giving me 30 days for each month where there are no LOAs listed (blank cells). I would like those to come out as 0.

 

[r1c1]

@Leigh Digons

Welcome to Chandoo.org forum and thanks for posting your question.

At the outset, your formulas for calculating Days OOO (out of office?) and Days in Office seem very long and complicated. I may be wrong in interpreting their full power and intentions.

Here is a simpler formula to calculate Days in Office, that seems to work ok:

=MAX(MIN(C8,$S$6)-MAX(B8,$R$6)+1-(I8-H8),0)

Once you know days in office, days OOO is simply

=DAY($S$6)- Days in Office

Hope that helps.

 

[Leigh Digons]

Hi this works. How would I modify it to include all of the LOAs? Just do + and the formula for those cells?

 

[Leigh Digons]

Hmm didn't work for multiple LOAs or blank LOAs.

 

[Leigh Digons]

Actually your formula also doesn't work if the LOA is outside of the month of January. It still counts takes away the days out of office in January even if the LOA is in March 8 - March 22nd for example.

 

[r1c1]

Actually your formula also doesn't work if the LOA is outside of the month of January. It still counts takes away the days out of office in January even if the LOA is in March 8 - March 22nd for example.

As per your example and your original formula, you are only looking at LOA data in first 2 columns. So I assumed there will be only one LOA per month.

Also, since you have 3 sets of LOA columns, I assumed each column refers to one month.

If multiple LOAs can be entered, your input data structure is not the best way to capture them. I suggest setting up a separate LOA table that has 3 columns - Emp name, start and end.

We can then use some sort of SUMIFS formula to calculate the total LOA with in a month.