# calculate number of days between two date within a current month including end date

1. ## calculate number of days between two date within a current month including end date

I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.

Could any help please!!! Many thanks.   Register To Reply

2. ## Re: calculate number of days between two date within a current month including end da

Hi,

You can use NETWORKDAYS function for that, but you will need to load the Analysis Tool Pak

http://office.microsoft.com/en-gb/ex...277241033.aspx

=NETWORKDAYS(A1,B1)  Register To Reply

3. ## Re: calculate number of days between two date within a current month including end da

Thanks oldchippy. I tried using networkdays function. However, this function only shown how many days from the start and end date excluding weekends.

But, i would need to calculate how many days fell within the month. For example, if the staff on leave from 31st March to 6 April, i need to show that leave taken in March = 1 day and leave taken in April = 4 days.

Anyone help please   Register To Reply

4. ## Re: calculate number of days between two date within a current month including end da

Hi Ahgoon,

Perhaps this will work for you. Let's assume you have the start date in cell A1 and the end date in A2.

In another cell, to get the number of days leave of the month in A1, use the formula:
=IF(MONTH(A1)=MONTH(A2),NETWORKDAYS(A1,A2),NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1)+1,0)))

Then, to get the number of days leave from the second month, in another cell use the formula:
=IF(MONTH(A1)=MONTH(A2),0,NETWORKDAYS(DATE(YEAR(A2),MONTH(A2),1),A2))

Obviously, if both dates are in the same month, the second formula will return 0.

Hope that helps!  Register To Reply

5. ## Re: calculate number of days between two date within a current month including end da

Thank you Pjoaquin.

I not very good with excel forumla.

However, how should i set the forumla if the start date and end date is more than 2 months i.e. 31 March to 01 May? How to calculate how many days fell within each month (March, April, May) including the end date and exclude weekends?

I have attached a file as a format example.  Register To Reply

6. ## Re: calculate number of days between two date within a current month including end da

Hi again,

This RIDICULOUSLY long (and somewhat hard to follow) formula should do the trick for any leave within 1 month, crossing over two months (e.g. Feb-Mar), or crossing over 3 months (e.g. Feb-Apr). It should even handle when your dates go from one year to the next (e.g. Dec '09-Jan '10). If you have any leave longer than that someone's going to have to come up with a better formula (or use a macro)!

First, I changed the month names in C1:N1 to actual text, not dates. "Jan", "Feb", "Mar", etc. (all 3 characters) through "Dec".

In C2 I entered the formula: ``Please Login or Register  to view this content.``
Then fill that formula right to cell N2. Select C2:N2 and fill the formula down as many rows as you need.

Hopefully that helps!!   Register To Reply

7. ## Re: calculate number of days between two date within a current month including end da

This formula should work with your original sheet its about 1/3 smaller than the previous and should handle any date ranges:- ``Please Login or Register  to view this content.``  Register To Reply

8. ## Re: calculate number of days between two date within a current month including end da

Hi Squiggler,

Thanks for improving the formula. I found a few instances where it is returning the wrong number of dates. Here's some examples:

If I enter 2/24/09 to 4/3/09, the results should be:
February: 4 (24-27)
March: 22 (2-6, 9-13, 16-20, 23-27, 30-31)
April: 3 (1-3)

Your formula is returning 3, 23, 2.

If I enter 3/31/09 to 8/6/09, the results should be:
March: 1
April: 22
May: 21
June: 22
July: 23
August: 4

Your formula is returning 1, 22, 21, 22, 22, 5

Thoughts?  Register To Reply

9. ## Re: calculate number of days between two date within a current month including end da

Based on the original setup, with the 1st of each month in C1 across, you can use this formula in C2 copied across and down

=MAX(0,NETWORKDAYS(MAX(\$A2,C\$1),MIN(\$B2,EOMONTH(C\$1,0))))

If you don't want to show zeroes custom format the result cells as 0;;

see attached  Register To Reply

10. ## Re: calculate number of days between two date within a current month including end da

DDL is a flippin' genius. ::bowing::  Register To Reply

11. ## Re: calculate number of days between two date within a current month including end da Originally Posted by daddylonglegs Based on the original setup, with the 1st of each month in C1 across, you can use this formula in C2 copied across and down

=MAX(0,NETWORKDAYS(MAX(\$A2,C\$1),MIN(\$B2,EOMONTH(C\$1,0))))

If you don't want to show zeroes custom format the result cells as 0;;

see attached

Incredible! Double-bow to DLL! I came across this after 1.5yrs of its original posting and find it very useful.

A curiosity question:
Why use "=MAX(0", instead of directly using =NETWORKDAYS(MAX(\$A2,C\$1),MIN(\$B2,EOMONTH(C\$1,0))). I removed the Max and didn't find difference in the result.

What scenario would having "=MAX(0" be helpful in?

- Nona  Register To Reply

12. ## Re: calculate number of days between two date within a current month including end da

Without the external MAX the NETWORKDAYs will return negative results wherever the date range is "out of date bounds".

In dll's sample with outer MAX removed the negative values would not be visible c/o the Custom Number format applied to the results matrix, should you rever to a General format you would see the negative integers.  Register To Reply

13. ## Re: calculate number of days between two date within a current month including end da

At last I seem to have found an easier calculation than one I was using - thanks very much  Register To Reply

14. ## Re: calculate number of days between two date within a current month including end date

why not just use =days360(\$A1,\$B1)?  Register To Reply

15. ## Re: calculate number of days between two date within a current month including end da Originally Posted by daddylonglegs Based on the original setup, with the 1st of each month in C1 across, you can use this formula in C2 copied across and down

=MAX(0,NETWORKDAYS(MAX(\$A2,C\$1),MIN(\$B2,EOMONTH(C\$1,0))))

If you don't want to show zeroes custom format the result cells as 0;;

see attached

Can this be done by using regular calendar days instead of NETWORKDAYS? I tried using DAYS but received an invalid number.  Register To Reply