Posts by Boycie92

    Re: Number of Calendar Days between the first Start Date & 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

    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