Adding an extra condition to an IF statement

[Gooford]

=IF(SUM(Q4)=0,"",MAX(0,G4-MAX(F4,Q4))/7) - to calculate number of days for year

=IF(SUM(TODAY()>G4),(G4-MAX(F4,Q4))/7,SUM(TODAY()-MAX(F4,Q4))/7) - to calculate number of days year to date


I am using this condition to calculate the length of a service which falls with in a specific review period, eg

Service starts 1 Feb 09 and finishes 30 April 09

The review period is 1 Mar 09 and finishes 31 May 09.

I would need to measure the number of days that fall within the review period, which would be 1 Mar 09 - 30 April 09

At the moment I am missing a condition which identifies whether to subtract from 'service end date' or 'review end date'.

Difficult to explain, sorry if its not clear, I'll supply further info if needed.

Any help much appreciated thanks

[DonkeyOte]

It's difficult because few references in the narrative within your file actually tie out to the actual cell references in use, nor do you outline expected results so it's hard to follow the logic.

On an aside, your existing formula in U4 can be simplified somewhat to:

=(MIN(G4,TODAY())-MAX(F4,Q4))/7
copied down

Perhaps worth posting a replacement file with corrected references and expected results (calculated manually of course)

[Gooford]

My apologies, I think I messed up copying and pasting the formula.

Part 1
IF(SUM(TODAY()>G6),(G6-MAX(F6,Q6))/7,SUM(TODAY()-MAX(F6,Q6))/7)
G6=Service End Date
F6 = Service Start Date
Q6 = Review Start Date

If today's date is greater than the service end date then subtract whichever is greater out of service start date or review start date.

17 Nov 09 is not greater than 28 Feb 10, therefore

17 Nov 09 minus 1 April 09 (as it is greater than Review Start date, 15 May 08)

= 230 days / 7 = 32.86 weeks


Part 2
=IF(SUM(Q6)=0,"",MAX(0,G6-MAX(F6,Q6))/7

The record 'Wrathal' shows 'service start date' (F6) of 1 Apr 09 and 'service end date' of 28 Feb 10 (G6).

F6 Service Start Date is greater than Q6 Review Start Date

'Review End Date' is 1/1/10 (R6) so is after G6 and can be disregarded

I manually calculated the result would be:-

28/2/10 (service end date - 1/4/09 (service start date) = 333 days / 7 = 47weeks

How do I deal with adding an extra condition to calculate whether the end date to use as the point from which to subtract should be the End Review Date or Service End Date.

[Gooford]

attachment with incorrect removed

Thanks

[DonkeyOte]

I understand how part one works (hence the shortened version offered in prior post), however, again I'm struggling on part two...

F6 Service Start Date is greater than Q6 Review Start Date

Agreed, this is true of all records.

'Review End Date' is 1/1/10 (R6) so is after G6 and can be disregarded

Agreed in so far as R6 is 1/1/10 but I disagree re: 2nd statement given G6 is 28/02/2010 and thus R6 is in fact before G6.

Even if you accept logic is the other way around (ie use lesser of G6/R6) in the formula you're presently using you calculate based of lesser of Today and the "other" date.
So even if the "other" date is the lesser of R6/G6 it still follows that if the lesser of those two values is beyond Today you would still use the current date in your calculation and thus result would remain as 32.86

[Gooford]

Oh thats pretty clumsy sorry yes.

I will work with the simplified formula you've given me and see if I can work it out.

Headache pills needed

Thanks

[Gooford]

Hi,

Just to let you know, using =(MIN(G4,TODAY())-MAX(F4,Q4))/7 and variations thereof, I was able to complete the task.

Thanks very much for your help, this simplified everything I was trying to do and took away the headache!