# Thread: calculation of dates (XP & 2003)

1. ## 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”

2. ## Re: calculation of dates (XP & 2003)

Try this formula in B4:

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

and fill down.

3. ## 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. ## Re: calculation of dates (XP & 2003)

<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. ## 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. ## 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
•