Help with formula to stop counting days when a value is entered into another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1120838%22%20slang%3D%22en-US%22%3EHelp%20with%20formula%20to%20stop%20counting%20days%20when%20a%20value%20is%20entered%20into%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120838%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20my%20first%20time%20to%20ask%20a%20question%20so%20hopefully%20I%20am%20explaining%20my%20question%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20IF%20statement%20that%20counts%20down%20days%20based%20on%20a%20start%20date%20(L)%20and%20target%20date%20(BE)%20that%20is%20dependent%20on%20whether%20or%20not%20a%20date%20is%20entered%20into%20(AB).%26nbsp%3B%20The%20target%20date%20(BE)%20is%20based%20on%20a%20specified%20number%20of%20days%20in%20(BC)%20added%20to%20the%20start%20date%20(L).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20my%20formula%20that%20is%20in%20(BD)...%3C%2FP%3E%3CP%3E%3DIF(AB2%3D%22%22%2CBE2-TODAY()%2C%3CFONT%20color%3D%22%23000000%22%3EAB2-L2)%3C%2FFONT%3E%3C%2FP%3E%3CP%3EThe%20%3CFONT%20color%3D%22%23FF0000%22%3EAB2-L2%26nbsp%3B%3CFONT%20color%3D%22%23000000%22%3Eis%20the%20part%20of%20the%20formula%20i%20want%20to%20change%20but%20don't%20know%20how.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CFONT%20color%3D%22%23000000%22%3EOnce%20a%20date%20is%20entered%20into%20(AB)%2C%20I%20would%20like%20(BD)%20to%20show%20the%20difference%20between%20the%20number%20of%20days%20in%20(BC)%20and%20the%20actual%20number%20of%20days%20it%20took%20to%20get%20to%20the%20target%20date%20(BE).%26nbsp%3B%20My%20current%20formula%20shows%20the%20actual%20number%20of%20days%20in%20(BD)%20but%20then%20you%20have%20to%20manually%20compare%20the%20days%20shown%20in%20(BC)%20and%20(BD).%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1120838%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1121094%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formula%20to%20stop%20counting%20days%20when%20a%20value%20is%20entered%20into%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F525990%22%20target%3D%22_blank%22%3E%40YeatsN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20a%20file%20with%20a%20simplified%20example%20that%20may%20contain%20the%20answer%20to%20your%20question.%20If%20not%2C%20please%20provide%20n%20example%20where%20you%20indicate%20what%20values%20you%20expect%20to%20see%20in%20a%20given%20situation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1121658%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formula%20to%20stop%20counting%20days%20when%20a%20value%20is%20entered%20into%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121658%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F525990%22%20target%3D%22_blank%22%3E%40YeatsN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimplified%20the%20formula%20a%20bit.%20See%20attached.%20Is%20that%20what%20you%20mean%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1121146%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formula%20to%20stop%20counting%20days%20when%20a%20value%20is%20entered%20into%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20actual%20%23%20of%20days%20go%20beyond%20the%20target%20number%2C%20how%20do%20I%20get%20those%20to%20show%20as%20a%20negative%20number%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

This is my first time to ask a question so hopefully I am explaining my question correctly.

 

I have an IF statement that counts down days based on a start date (L) and target date (BE) that is dependent on whether or not a date is entered into (AB).  The target date (BE) is based on a specified number of days in (BC) added to the start date (L).

 

Below is my formula that is in (BD)...

=IF(AB2="",BE2-TODAY(),AB2-L2)

The AB2-L2 is the part of the formula i want to change but don't know how.

 

Once a date is entered into (AB), I would like (BD) to show the difference between the number of days in (BC) and the actual number of days it took to get to the target date (BE).  My current formula shows the actual number of days in (BD) but then you have to manually compare the days shown in (BC) and (BD).

 

 

4 Replies

@YeatsN 

Attached a file with a simplified example that may contain the answer to your question. If not, please provide n example where you indicate what values you expect to see in a given situation.

@Riny_van_Eekelen 

Thanks for the help.  

 

If the actual # of days go beyond the target number, how do I get those to show as a negative number?

@YeatsN 

Simplified the formula a bit. See attached. Is that what you mean?