Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    calculation of dates (XP & 2003)

    I am trying to find a way of determining a percent using the number of days from a specific date to a fixed date. This has to be in the form of an "IF" statement due to the fact the specific date is either the Date of Termination or the Date of Hire.

    I am including an attachment demonstrating the worksheet.
    In the “FY08 Budget Impact” cell(fixed date), the following if statement has to be calculated. If there is either a “Date of Termination” or a “Date of Hire”.

    Calculate the number of days from either “Date of Termination” or Date of Hire” and as a percentage of the total year days. Then multiply this percentage by the “Annual Budget Impact”
    Attached Files Attached Files

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

    Re: calculation of dates (XP & 2003)

    Try this formula in B4:

    =($B$1-MAX(C44))/365*A4

    and fill down.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: calculation of dates (XP & 2003)

    Thanks for the formula. It does work well. Could it also be done with "IF" statements. if you have time could you demonstrate it.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calculation of dates (XP & 2003)

    How about this:
    <pre>=($B$1-IF(ISBLANK(C4),D4,C4))/365*A4</pre>


    I prefer NOT using an IF as it is more calc/memory intensive

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: calculation of dates (XP & 2003)

    The formula that Hans established worked well. However, I think an if statement is required in case there is no "date of termination" and no "date of Hire". Then no calculation should be made until one of those date fields are populated. That is why an if statement is important here.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculation of dates (XP & 2003)

    If you had included an example where both dates are blank in your sample workbook, we'd have been alerted to that possibility. Since you didn't bother to do so, we had no idea it would be possible.

    Try this:

    =IF(AND(ISBLANK(C4),ISBLANK(D4)),"",($B$1-MAX(C44))/365*A4)

Posting Permissions

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