Conditional date calculation

  • I need a formula that will find the number of weeks between two dates, but with conditions. Dependent upon the Date Started and Date Term, we need to determine the number of weeks worked between 1/2/2010 - 1/2/2011, and also between 1/3/2011 - 8/8/2013. If the date term is blank, the calculcation should use 1/2/2011 and 8/8/2013 respectively. The calculation can just find the number of days and divide by 7.

  • Re: Conditional date calculation

    Thanks, Yegarboy. Since I only need the number of weeks between two dates, the formula needs to test for start date before 1/2/2010 and end date later than 1/2/2011. If both of those are true, then it needs to calculate the weeks worked within the two ranges (1/2/10 - 1/2/11 and 1/3/11 - 8/8/13). I get that some of the rows will just be "52" in column C (C3 thru c15 are like this). But when I get to C16, I'm not sure how to calculate number of weeks should be from 1/2/10 to 9/27/10 only for C16. If I can get something working for column C, then I can update and reuse formula for column D.

    So for column C, if start date < 1/2/2010, and term date either blank or > 1/2/2010, then 52, else the calculation needs to figure out if the start date and term date are within 1/2/2010 and 1/2/2011. If so, then use the actual dates, but if not, then use either the default 1/2/2010 or 1/2/2011 respectively.

    There's not that many rows, so I may just filter and adjust calculation according to the filtered data.

    Thanks again for assisting me. Much appreciated.


Participate now!

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