+ Reply to Thread
Results 1 to 7 of 7

Days in each month for a time span

  1. #1
    Registered User
    Join Date
    12-15-2013
    Location
    nyc
    MS-Off Ver
    excel 2010
    Posts
    6

    Days in each month for a time span

    I am looking for a formula that will tell me how many days in each month for which a given time span is active. For example the time span 1/1/2014-2/28/14. I want to know the time span is active for 31 days in January and 28 days in February. Another example: time span of 1/15/2014-2/28/2014. I want to know that the time span would be active for 16 days in January and 28 days in February. I am looking for a formula that will capture the amount of days in each month for which the time span covers. Please help, as I only know how to write a formula with multiple if statements that require a different formula for each given month, rather than one formula that applies to all months. Thank you in advance for your suggestions and assistance.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Days in each month for a time span

    If you're counting the start date then for 1/15/2014-2/28/2014 there would be 17 days for January.

    Try this...

    Data Range
    A
    B
    C
    D
    E
    1
    Start
    End
    Month/Year
    Days
    2
    1/15/2014
    2/28/2014
    January 2014
    17
    3
    February 2014
    28
    4
    ------
    ------
    ------
    ------
    ------


    Enter this formula in D2:

    =IF(DATE(YEAR(A$2),MONTH(A$2)+ROWS(D$2:D2)-1,1)<B$2,TEXT(DATE(YEAR(A$2),MONTH(A$2)+ROWS(D$2:D2)-1,1),"mmmm yyyy"),"")

    Enter this formula in E2:

    =IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS(E$2:E2)-1,1))<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROWS(D$1:D2)-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS(D$2:D2)-1,1))+1,"")

    Select D2:E2 and copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    12-15-2013
    Location
    nyc
    MS-Off Ver
    excel 2010
    Posts
    6

    Re: Days in each month for a time span

    Thank you Tony for your quick response. The solution works perfectly; however, is there anyway to get the month and year in d2 and d3 to go horizontal in d1 and e1 and have the days under that in d2 and e2 respectively?

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Days in each month for a time span

    Like this...

    Data Range
    A
    B
    C
    D
    E
    1
    Start
    End
    ------
    January 2014
    February 2014
    2
    1/15/2014
    2/28/2014
    17
    28


    Enter this formula in D1:

    =IF(DATE(YEAR($A2),MONTH($A2)+COLUMNS($D1:D1)-1,1)<$B2,TEXT(DATE(YEAR($A2),MONTH($A2)+COLUMNS($D1:D1)-1,1),"mmmm yyyy"),"")

    Enter this formula in D2:

    =IF(MAX($A2,DATE(YEAR($A2),MONTH($A2)+COLUMNS($D2:D2)-1,1))<$B2,MIN(DATE(YEAR($A2),MONTH($A2)+COLUMNS($D2:D2)+1-1,0),$B2)-MAX($A2,DATE(YEAR($A2),MONTH($A2)+COLUMNS($D2:D2)-1,1))+1,"")

    Select D1:D2 and copy across until you get blanks.

  5. #5
    Registered User
    Join Date
    12-15-2013
    Location
    nyc
    MS-Off Ver
    excel 2010
    Posts
    6

    Re: Days in each month for a time span

    Thats it, perfect. Thanks Tony, I've only posted twice and you helped me out both times, your a lifesaver. I sincerely appreciate the help.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Days in each month for a time span

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Days in each month for a time span

    Another variation...........

    Assuming A2 has start date and B2 has end date, then in C2 place the formula given below and drag horizontally until you get blank cells.
    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. find time left in month.days unit between two dates(in YEAR.MONTH.DAY)
    By xcfeng95 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-21-2014, 12:55 PM
  2. [SOLVED] Counting the number of days per month where where a job start time was after 10:00
    By Apache_sim in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-15-2013, 07:59 AM
  3. Alot monthly volumes to particular days by weeks that span months
    By Vaslo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-22-2013, 06:13 PM
  4. Chart each hour over a 12 month span
    By andrewmo in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-02-2012, 07:27 PM
  5. [SOLVED] Dividing a time span into shifts - overlapping days
    By Heidi in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-27-2006, 10:14 PM

Tags for this Thread

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