<365 days then....

[Fester675]

Hi - I'm stuck!!

Here is my formula... in D12

=IF(S8<365,(D11/365)*S8,IF(B6="Location","",IF(B6="London",D11*7.2,IF(B6="National",D11*7.4)))))

So 'S8' calculates number of days between today and date inputed by user; if this is less than 365 the user's entitlement (D11) is divided by 365 then multiplied by the number of days in S8.

The result in D12, then needs to be multiplied either by 7.2, or 7.4 dependent on the final 2 arguments.

TIA

 

[Scott Huish]

Because there is no FALSE parameter in your last IF, I am assuming B6, if not Location, can only be London or National.

Does this work for you?

=IF(B6="Location","",(D11*(IF(B6="London",7.2,7.4)))*(IF(S8<365,(D11/365)*S8,1)))

 

[Fester675]

It's nearly there Scott, but not quite! It returns 000's of hours!

Here is what I'm after, the long winded way...

If B6 = "Location" then D12 = "";
If B6 = "London" then D12 = D11 * 7.2;
If B6 = "National" then D12 = D11 * 7.4;

However....

If S8 is < 365 days then D12 = (D11/365)*S8

 

[Scott Huish]

The "long winded way" makes more sense to me

=IF(B6="Location","",IF(S8<365,(D11/365)*S8,D11*IF(B6="London",7.2,7.4)))

 

[Fester675]

Thanks Scott!!