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

**ahgoon** · 04-02-2009, 04:42 AM

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.

**oldchippy** · 04-02-2009, 05:38 AM

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)

**ahgoon** · 04-02-2009, 09:14 PM

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

**Paul** · 04-02-2009, 09:54 PM

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!

**ahgoon** · 04-02-2009, 11:25 PM

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.

Many thanks for your help.

**Paul** · 04-03-2009, 12:26 AM

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!!

**squiggler47** · 04-03-2009, 03:22 AM

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.

**Paul** · 04-03-2009, 01:45 PM

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?

**daddylonglegs** · 04-03-2009, 06:34 PM

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

**Paul** · 04-03-2009, 10:04 PM

DDL is a flippin' genius. ::bowing::

**anonnona** · 06-08-2010, 05:25 AM

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

**DonkeyOte** · 06-08-2010, 05:36 AM

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.

**Tcooper55** · 05-21-2013, 10:59 AM

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

**STIN01** · 10-09-2013, 12:13 PM

why not just use =days360($A1,$B1)?

**jrmarx** · 04-06-2015, 09:19 PM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions