If Statement with multiple criteria

**Ada01** · 11-11-2009, 03:32 PM

My concern is in row 4 and 5 of the attached spreadsheet. The problem is that not all postings for the allocations will take place on or before the date the allocation starts. Therefore, i need a logical formula that builds on what already exists and determines if the "entered" date is > the "start date". and then catches up how far it was behind.

Example:

contract 12,000
Contract starts January 1st and ends December 31 (12 Months)
Entered Feb 1

In the January column there chould be zero in feb there should be 2,000 and the rest of the months should be 1,000

**Richard Buttrey** · 11-11-2009, 03:44 PM

Hi,

In K4 and copied across

Please Login or Register  to view this content.

Rgds

**Ada01** · 11-13-2009, 11:12 AM

That works great, however, when the Start date is the same as the entered date, then it still takes does the additional month allocation when it should only be doing a single month.

I have tried to modify this in several ways to make things work out (change the criteria, or modify the column()), but all have fallen short

any suggestions?

Thanks

**DonkeyOte** · 11-13-2009, 03:51 PM

FWIW I'd suggest storing total months of project somewhere... for sake of demo let's book in I5

Please Login or Register  to view this content.

The above is to negate need for repetitive calcs

Please Login or Register  to view this content.

there are numerous other approaches...

another for ex. would be to calculate cumulative total to date for each month (by taking total and apportioning based on % complete) and obtaining periodic hit by subtracting prior cumulative balance (would require Ix always be 0)

**Ada01** · 11-19-2009, 10:26 AM

I never knew the DATEDIF() existed. I will be using that in A LOT of spreadsheets - it cuts the length of the formula in half in many cases!!!

Thanks A Bunch