Kind of a strange IF statement question here...

bran987

New Member
Joined
Jan 10, 2005
Messages
45
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
First I think your Example 4 is wrong and should return 5, not 3 because it was 5 days late.

I've come up with this and it works with all your examples. Replace A3 is blank with whatever you want.
Excel Workbook
AB
110
201-Jan-08
301-Jan-08
Sheet2


<A3),0,IF(A3<A2,"-1",IF(A3<(A2+A1),0,A3-A2)))))[ p code]<>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top