Number of Calendar Days between the first Start Date & the last End Date

    Hi,



    I was wondering if someone can help me.


    I have this formula

    = IFERROR(DATEDIF(B9,INDEX(C9:C999,MATCH(9.999E+307,C9:C20999)),"D")+1,0)


    It is designed to count the Number of Calendar Days between the first Start Date (Column B) & the last End Date (Column C)


    However I have another column, column D that has certain terms. E.g "Exempt" I want the formula to ignore rows/dates that have this term.


    So if it says "Exempt" In B9 The start date should be B10 if it says "Exempt" in C15 the end Date should be C14.


    Is it possible to achieve this?


    Thanks for the help,
    Boycie92

    Re: Number of Calendar Days between the first Start Date & the last End Date


    Could do this array:


    =MAX(IF(D2:D999<>"Exempt",C2:C999))-MIN(IF(D2:D99<>"Exempt",B2:B999))


    Remember to confirm using Ctrl+Shift+Enter, not just Enter. Side note, I'm not exactly sure where you data is, so may need to adjust range sizes. Just make sure all the ranges are the same size.

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Quote from Luke M;795335

    Could do this array:


    =MAX(IF(D2:D999<>"Exempt",C2:C999))-MIN(IF(D2:D99<>"Exempt",B2:B999))


    Remember to confirm using Ctrl+Shift+Enter, not just Enter. Side note, I'm not exactly sure where you data is, so may need to adjust range sizes. Just make sure all the ranges are the same size.


    Hi


    Thanks for this. It works like a charm! is it possible to make it dynamic so if my ranges expand the formula will include the new rows?


    Thanks,
    Boycie92

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Can you use an XL table, so that you can use a structural reference (Insert - Table)?

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Hello Luke :wink:


    What about a simple Named Range ... defined dynamically ... ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Hi,


    Both of those solutions worked. Thank you.


    Quick Question. Is there a way i could also use this formula and change it so it would only show the number of months between the first start date and the last end date?


    thanks again for all the help!


    Boycie92

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Hi,


    As a sample for the number of months ... to be adjusted to your specific ranges ...


    Code
    =DATEDIF(MIN(IF(D2:D10<>"Exempt",B2:B10)),MAX(IF(D2:D10<>"Exempt",C2:C10)),"m")


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Quote from Carim;795342

    Hello Luke :wink:


    What about a simple Named Range ... defined dynamically ... ?

    Pure genius! :P

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Let's hope the Datedif() formula does calculate the correct number of months ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    If not, an alternative structure:


    =INT(DAYS360(FirstDateFormula, LastDateFormula)/30)

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Quote from Luke M;795335

    Could do this array:


    =MAX(IF(D2:D999<>"Exempt",C2:C999))-MIN(IF(D2:D99<>"Exempt",B2:B999))


    Remember to confirm using Ctrl+Shift+Enter, not just Enter. Side note, I'm not exactly sure where you data is, so may need to adjust range sizes. Just make sure all the ranges are the same size.



    Is there anyway to edit this formula so that if my table included blank rows (Start Date & End Date) to ignore them. At the moment the formula is using the blank values and giving me the wrong difference?


    Thanks,
    Boycie92

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    Try this:


    =MAX(IF(D2:D999<>"Exempt",IF(C2:C999<>"",C2:C999)))-MIN(IF(D2:D99<>"Exempt",IF(B2:B999<>"",B2:B999)))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Number of Calendar Days between the first Start Date &amp; the last End Date


    You've just got lucky, that's all! ;)


    For future reference, and as the requests become more complex, attaching a workbook will often get a faster response.


    There's a little star below my avatar at the bottom of the post - you can thank anyone who helps you by adding to their reputation. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login