Results 1 to 5 of 5

Thread: if function

  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    if function

    Hi,
    i want to calculate,if a start date is less than or equal to 31/12/2006, then it must be 0, and if the end date is more than or equal to 31/03/2007 then the salary must be divided by 12. how do i calculate this? I have attached an example of this.
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: if function

    Put 31/12/2006 in G2, and 31/03/2007 in H2. The formula in E2 wouid be

    =IF(A2<=$G$2,0,IF(B2>=$H$2,D2/12,0))

    You can fill this down as far as needed.

    Note: since all start dates in your sample workbook are less than or equal to 31/12/2006, all results will be 0. See attached workbook.
    Attached Files Attached Files

  4. #3
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if function

    Hi Hans,
    It works for some but some of the dates that end on the 31/05/2007, it gives me zero for them. i have higlighted two examples, they should get a bonus because their termination date is more than 31/03/2007. I have attached another one where I calculated the formula you gave me.

    Thanks,
    Sharon
    Attached Files Attached Files

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: if function

    Your first requirement is "if a start date is less than or equal to 31/12/2006, then it must be 0". In the examples you highlighted, the start dates are 1 July 1996 and 1 May 2006. Both are before 31 December 2006, so according to the first requirement, the bonus is 0. Did you mean "if a start date is greater than or equal to 31/12/2006"? If so, change the formula in H3 to

    =IF(A3>=$H$1,0,(IF(B3>=$I$1,D3/12,0)))

    and fill down.

  6. #5
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if function

    Thanks Hans!

Posting Permissions

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