+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    04-01-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2000
    Posts
    4

    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.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    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)
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    04-01-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2000
    Posts
    4

    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

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

    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!

  5. #5
    Registered User
    Join Date
    04-01-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2000
    Posts
    4

    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.

    Many thanks for your help.
    Attached Files Attached Files

  6. #6
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

    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!!
    Last edited by Paul; 04-03-2009 at 12:28 AM.

  7. #7
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    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.
    Attached Files Attached Files

  8. #8
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

    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?

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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
    Attached Files Attached Files

  10. #10
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

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

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

  11. #11
    Registered User
    Join Date
    06-08-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003
    Posts
    1

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

    Quote Originally Posted by daddylonglegs View Post
    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

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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.

  13. #13
    Registered User
    Join Date
    05-21-2013
    Location
    Middlesex, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    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

  14. #14
    Registered User
    Join Date
    09-19-2013
    Location
    Iowa
    MS-Off Ver
    Excel 2007
    Posts
    1

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

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

  15. #15
    Registered User
    Join Date
    03-23-2015
    Location
    St. Louis, MO
    MS-Off Ver
    2013
    Posts
    1

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

    Quote Originally Posted by daddylonglegs View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1