OK I basically have 3 cells to deal with, let's call them A1, A2, and A3
A1: Can be any number 1 or greater (represents a number of days)
A2: Will be a date
A3: Will be a date OR blank
So I need an if statement combo that does this:
If A1 = 1 and A3 is equal to A2, return zero.
If A1 = 1 and A3 is blank, signal that in some way (any way, a date in numerical format etc. i.e. the 39,000 number that comes up when you subtract 0 from a date is fine)
If A1 = 1 and A3 is greater or less than A2, return the number of days that it's off.
ALSO
If A1 > 1, return 0 if A3 is blank AND A2 + A1 is less than A3
but
If A1 > 1 and the above is not true, return the number of days off of A2 that A3 is.
So let's give 8 examples:
Example 1:
A1 = 1
A2 = 1/2/2008
A3 = 1/2/2008
return 0
Example 2:
A1 = 1
A2 = 1/2/2008
A3 = 1/1/2008 or 1/3/2008
return the number of days off (-1 or +1)
Example 3:
A1 = 1
A2 = 1/2/2008
A3 = BLANK
return in some way that the task didn't happen, again a date in numerical format etc. i.e. the 39,000 number that comes up when you subtract 0 from a date is fine
THOSE ARE THE A1 = 1 EXAMPLES
--------
OK NOW EXAMPLES WHEN A1 > 1
Example 4:
A1 = 3
A2 = 1/2/2008
A3 = 1/7/2008
return the number of days past 1/2/2008 + 2 (1/2/2008 counts as day 1) that the task ended (so 1/2/2008 + 3 means 1/4/2008 was the latest day this task should've occurred, so return 3, it was 3 days late) i.e. task was supposed to happen on either 1/2/2008, 1/3/2008 or 1/4/2008, but it didn't actually happen until 1/7/2008
Example 5:
A1 = 3
A2 = 1/2/2008
A3 = 1/2/2008
return 0, the task happened on or before 1/2/2008 + 2 more days (1/2/2008 counts as day 1)
Example 6:
A1 = 3
A2 = 1/2/2008
A3 = 1/3/2008
Return 0 again! because we want a 0 always if A3 is less than A2 + the number of days in A1 (counting A2 as a day, so in this case we want to return 0 if A3 equals 1/2/2008, 1/3/2008, or 1/4/2008.)
Example 7:
A1 = 3
A2 = 1/2/2008
A3 = 1/1/2008
Return -1, the task should never happen early.
Example 8:
A1 = 3
A2 = 1/2/2008
A3 = BLANK
Return 0 here, I always want a 0 if A1 > 1 and A3 is blank.
ANY TIME A1 > 1 AND A3 IS BLANK I NEED A 0
ANY TIME A1 = 1 AND A3 IS BLANK I NEED THE NUMBER OF DAYS A3 IS OFF FROM A2!
A1 WILL NEVER BE LESS THAN 1.
Let me know if you have any questions I know that is confusing!
Basically we're trying to calculate whether or not a task happened within a certain range of days, with a 0 return message meaning it happened within that range specified (which is represented by A2 plus the number of days in A1) i.e. A3 should be within that range.
Thanks
A1: Can be any number 1 or greater (represents a number of days)
A2: Will be a date
A3: Will be a date OR blank
So I need an if statement combo that does this:
If A1 = 1 and A3 is equal to A2, return zero.
If A1 = 1 and A3 is blank, signal that in some way (any way, a date in numerical format etc. i.e. the 39,000 number that comes up when you subtract 0 from a date is fine)
If A1 = 1 and A3 is greater or less than A2, return the number of days that it's off.
ALSO
If A1 > 1, return 0 if A3 is blank AND A2 + A1 is less than A3
but
If A1 > 1 and the above is not true, return the number of days off of A2 that A3 is.
So let's give 8 examples:
Example 1:
A1 = 1
A2 = 1/2/2008
A3 = 1/2/2008
return 0
Example 2:
A1 = 1
A2 = 1/2/2008
A3 = 1/1/2008 or 1/3/2008
return the number of days off (-1 or +1)
Example 3:
A1 = 1
A2 = 1/2/2008
A3 = BLANK
return in some way that the task didn't happen, again a date in numerical format etc. i.e. the 39,000 number that comes up when you subtract 0 from a date is fine
THOSE ARE THE A1 = 1 EXAMPLES
--------
OK NOW EXAMPLES WHEN A1 > 1
Example 4:
A1 = 3
A2 = 1/2/2008
A3 = 1/7/2008
return the number of days past 1/2/2008 + 2 (1/2/2008 counts as day 1) that the task ended (so 1/2/2008 + 3 means 1/4/2008 was the latest day this task should've occurred, so return 3, it was 3 days late) i.e. task was supposed to happen on either 1/2/2008, 1/3/2008 or 1/4/2008, but it didn't actually happen until 1/7/2008
Example 5:
A1 = 3
A2 = 1/2/2008
A3 = 1/2/2008
return 0, the task happened on or before 1/2/2008 + 2 more days (1/2/2008 counts as day 1)
Example 6:
A1 = 3
A2 = 1/2/2008
A3 = 1/3/2008
Return 0 again! because we want a 0 always if A3 is less than A2 + the number of days in A1 (counting A2 as a day, so in this case we want to return 0 if A3 equals 1/2/2008, 1/3/2008, or 1/4/2008.)
Example 7:
A1 = 3
A2 = 1/2/2008
A3 = 1/1/2008
Return -1, the task should never happen early.
Example 8:
A1 = 3
A2 = 1/2/2008
A3 = BLANK
Return 0 here, I always want a 0 if A1 > 1 and A3 is blank.
ANY TIME A1 > 1 AND A3 IS BLANK I NEED A 0
ANY TIME A1 = 1 AND A3 IS BLANK I NEED THE NUMBER OF DAYS A3 IS OFF FROM A2!
A1 WILL NEVER BE LESS THAN 1.
Let me know if you have any questions I know that is confusing!
Basically we're trying to calculate whether or not a task happened within a certain range of days, with a 0 return message meaning it happened within that range specified (which is represented by A2 plus the number of days in A1) i.e. A3 should be within that range.
Thanks