SOLVED

Freeze or Stop Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1719595%22%20slang%3D%22en-US%22%3EFreeze%20or%20Stop%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1719595%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20an%20overview%20of%20the%20fleet%3A%20date%20of%20car%20in%20garage%2C%20number%20of%20days%20in%2C%20date%20back%20...%3C%2FP%3E%3CP%3EI%20used%20the%20'today'%20function%20to%20count%20how%20many%20days%20the%20car%20is%20in%20the%20garage.%20Now%20I%20would%20like%20that%20when%20I%20fill%20in%20the%20cell%20'date%20back'%2C%20the%20number%20of%20days%20no%20longer%20starts%20counting%2C%20because%20this%20is%20of%20course%20no%20longer%20necessary.%20So%20the%20formula%20should%20automatically%20freeze%20for%20this%20line%20when%20I%20fill%20in%20a%20particular%20cell.%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%2C%3CBR%20%2F%3EIna%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1719595%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1719637%22%20slang%3D%22en-US%22%3ERe%3A%20Freeze%20or%20Stop%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1719637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811425%22%20target%3D%22_blank%22%3E%40inawolters%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20Date%20In%20is%20in%20cell%20D2%2C%20Date%20Back%20in%20E2%2C%20and%20Number%20of%20Days%20in%20F2.%3C%2FP%3E%0A%3CP%3EUse%20the%20following%20formula%20in%20F2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(D2%3D%22%22%2C%22%22%2CIF(E2%3D%22%22%2CTODAY()%2CE2)-D2%2B1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1720308%22%20slang%3D%22en-US%22%3ERe%3A%20Freeze%20or%20Stop%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1720308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20reason%20this%20does%20not%20work.%20I%20get%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1720463%22%20slang%3D%22en-US%22%3ERe%3A%20Freeze%20or%20Stop%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1720463%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811425%22%20target%3D%22_blank%22%3E%40inawolters%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20to%20replace%20D2%20and%20E2%20with%20the%20cells%20that%20contain%20Date%20In%20and%20Date%20Back%2C%20of%20course.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S3547.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222573i1F63FD47C21585B7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22S3547.png%22%20alt%3D%22S3547.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20can't%20make%20it%20work%2C%20could%20you%20attach%20a%20small%20sample%20workbook%20without%20sensitive%2Fproprietary%20data%3F%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728613%22%20slang%3D%22en-US%22%3ERe%3A%20Freeze%20or%20Stop%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811425%22%20target%3D%22_blank%22%3E%40inawolters%3C%2FA%3E%26nbsp%3B%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EI'm%20sure%20this%20is%20what%20you%20are%20looking%20for%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1601463968996.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223031iC98D034A256887B8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rajesh-S_0-1601463968996.png%22%20alt%3D%22Rajesh-S_0-1601463968996.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EFormula%20in%20cell%20AC4%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(NOT(ISBLANK(AB4))%2C%22%22%2CDATEDIF(AA4%2CTODAY()%2C%22D%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_1-1601464142433.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223033iB6F6406D1FFD7B71%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rajesh-S_1-1601464142433.png%22%20alt%3D%22Rajesh-S_1-1601464142433.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CEM%3EAs%20soon%20you%20fill%20cell%20Date%20back%20with%20any%20data%2C%20may%20be%20Text%2C%20Number%20or%20Date%20even%2C%20you%20get%20blank%20in%20cell%20AC4.%3C%2FEM%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CEM%3EYou%20may%20use%20this%20one%20also%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(NOT(ISBLANK(AB4))%2C%22Vehicle%20Delivered%22%2CDATEDIF(AA4%2CTODAY()%2C%22D%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EAdjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FLI%3E%3CLI%3EIf%20this%20not%20what%20you%20are%20looking%20for%26nbsp%3B%20then%20please%20share%20some%20sample%20data%20with%20us.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E***%20In%20cease%20if%20this%20post%20solves%20the%20issue%2C%20please%20mark%20it%20as%26nbsp%3B%3CFONT%20size%3D%223%22%3EBest%20Solution.%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3CH1%20id%3D%22toc-hId-553456614%22%20id%3D%22toc-hId-553456614%22%20id%3D%22toc-hId-553456614%22%20id%3D%22toc-hId-553456614%22%3E%26nbsp%3B%3C%2FH1%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1733745%22%20slang%3D%22en-US%22%3ERe%3A%20Freeze%20or%20Stop%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1733745%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20It%20works!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello!

 

I have created an overview of the fleet: date of car in garage, number of days in, date back ...

I used the 'today' function to count how many days the car is in the garage. Now I would like that when I fill in the cell 'date back', the number of days no longer starts counting, because this is of course no longer necessary. So the formula should automatically freeze for this line when I fill in a particular cell.

Can anyone help me with this?

 

Greetings,
Ina

6 Replies
Highlighted

@inawolters 

Let's say Date In is in cell D2, Date Back in E2, and Number of Days in F2.

Use the following formula in F2:

 

=IF(D2="","",IF(E2="",TODAY(),E2)-D2+1)

 

This can be filled down.

Highlighted

@Hans Vogelaar 

 

For some reason this does not work. I get an error.

 

 

Highlighted

@inawolters 

You have to replace D2 and E2 with the cells that contain Date In and Date Back, of course.

 

S3547.png

 

If you can't make it work, could you attach a small sample workbook without sensitive/proprietary data?

Highlighted
Best Response confirmed by inawolters (New Contributor)
Solution

@inawolters ,,

 

I'm sure this is what you are looking for:

 

Rajesh-S_0-1601463968996.png

 

  • Formula in cell AC4:

 

=IF(NOT(ISBLANK(AB4)),"",DATEDIF(AA4,TODAY(),"D"))

 

 

Rajesh-S_1-1601464142433.png

 

  • As soon you fill cell Date back with any data, may be Text, Number or Date even, you get blank in cell AC4.

You may use this one also:

 

 

=IF(NOT(ISBLANK(AB4)),"Vehicle Delivered",DATEDIF(AA4,TODAY(),"D"))

 

 

  • Adjust cell references in the formula as needed.
  • If this not what you are looking for  then please share some sample data with us.

 

*** In cease if this post solves the issue, please mark it as Best Solution.

 

Highlighted

@Rajesh-S 

 

Thank you! It works!

Highlighted
Glad to help you,, since what I've suggested is working so if you feel then you may mark my post/answer as,, Best Response ☺