I need help producing a report that shows number of days within a certain date range

Hi All,

I am currently writing a report to show the absence per month by employees, for 1200 employees! So ther report is by month, for june, july, august and september.

Some of the employees have absences longer than a month e.g 14/04/2012 - 27/07/2012. This creates a problem as the total days that it shows aren't correct, as it will show the total - not just for June.

Does anyone have a formula or a theory as to how I can work this, without doing it manually??

I have attached the spreadsheet for you to look at. If you can help, it would be amazing!! and save me so much time.

Thanks,

Emily

Check out this workbook. Is this what you are looking for? The NETWORKDAYS formulas that I used don't count weekend days.

The result is pretty pretty but the formula is not...

It wouldn't surprise me if some smart person on this forum could take that formula down to something more compact, that would be cool. I started filling in holidays but you have to finish it. The holidays are in a named range. You know that little window up to the left where the address of your current cell is shown? Click that arrow and you'll see the named range. You may have to expand it. Should have been dynamic but I don't master that yet.

Also there is some conditional formatting, it just made it easier on the eyes to tone down those zeros.

This version should give the same results....

=MAX(0,NETWORKDAYS(MAX($A11,D$2+1),MIN($B11,E$2),Holidays))

Glad I could help! Looking forward to even more feedback, Emily. I hope this forum will inspire you to learn Excel.

Sorry daddylonglegs, your formula does not produce the same result. I get numbers between 19 and 23 on each month. Love to see a smarter formula though. I just have a feeling it is possible.

Originally Posted by Jacc

Sorry daddylonglegs, your formula does not produce the same result.

It normally would but the "dates" in columns A and B are actually text formatted - try ISNUMBER(A11) to check - so that messes with my MIN and MAX functions - this slightly tweaked version should work on the data "as is"

=MAX(0,NETWORKDAYS(MAX($A11+0,D$2+1),MIN($B11+0,E$2),Holidays))

or just convert columns A and B to real dates using text to columns and my original version works - see attached

I note that in some rows there is no end date - what should happen there? This version will return zero if there is no end date

=IF(LEN($A11)*LEN($B11),MAX(0,NETWORKDAYS(MAX($A11+0,D$2+1),MIN($B11+0,E$2),Holidays)),"")

Originally Posted by daddylonglegs

This version should give the same results....

=MAX(0,NETWORKDAYS(MAX($A11,D$2+1),MIN($B11,E$2),Holidays))

Now that I look at it with a clear mind (my last post was past bed time ) I see a very simple and easy to understand formula. Emily, are you taking notes? This is what it's supposed to look like.
Yes I saw that there was something funny with the format of the dates. I would fix the format and keep this simple formula.