Results 1 to 9 of 9

Thread: hi need help with IF forumala

  1. #1

    hi need help with IF forumala



    Register for a FREE account, and/
    or Log in to avoid these ads!

    can someone help me on IF formula on excel... I have two columns one column is "start date" and other is "end date", both columns have dates but not in order... and I would like to create another column which shows 2013 of jan feb march etc and in each month to show wither they have been active basically to show the number of days they have been active in each month....so if a person worked from 01-01-2010 to 03-02-2014 then in jan 2013 column it should show 31 days and feb 2013 colume should show 30 days and so fourth... is there is formula for this using IF ?? thanks.

  2. #2
    Try

    =SUMPRODUCT(--(ROW(INDIRECT($A2&":"&$B2))>=C$1),--(ROW(INDIRECT($A2&":"&$B2))<DATE(YEAR(C$1),MONTH(C$1)+1,1)))

    where C$1 is the comparison date cell, such as 1/1/2013

  3. #3
    omg mate I can see you've done this before ! amazing well done sir thank you for efforts appreciated !!!! if you lived local a pint is on me!!!

  4. #4
    omg mate I can see you've done this before ! amazing well done sir thank you for efforts appreciated !!!! if you lived local a pint is on me!!! slight problem....im using F COLUM start date and G column end date and I1 for 01/01/2013 however its showing me 0 for when the date is 03/01/2013 ,,,it should show 3.....? In the end date colum I do not have any dates in there which means the customer is working and when I drag the formula down it shows #reff....instead of showing #reff can you show the word "active" ??

  5. #5
    Try

    =IF($G2="",IF(TEXT($F2,"mmyy")=TEXT(I$1,"mmyy"),"Active",""),
    IF(OR(TEXT($F2,"mmyy")>TEXT(I$1,"mmyy"),TEXT($G2,"mmyy")<TEXT(I$1,"mmyy")),"",
    SUMPRODUCT(--(ROW(INDIRECT($F2&":"&$G2))>=I$1),--(ROW(INDIRECT($F2&":"&$G2))<DATE(YEAR(I$1),MONTH(I$1)+1,1)))))

  6. #6
    for some reason its showing just blank. whole formula to be entered in one cell ?
    Last edited by k13; 2015-02-05 at 01:13 PM.

  7. #7
    Yes, I adjusted it to reflect the start and end dates in F and G, the first check date in I.

    Because I made the formula multi-line, for easy reading, you need to enter it in the formula bar, not directly into the cell.

  8. #8
    not to worry about the active I managed to get the first formula working by pressing f4 to lock the 01/01/2013 cell

  9. #9
    Doesn't sound right, I already used C$1. If you lock that to $C$1 it won't adjust on the next column.

Posting Permissions

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