Freezing a result

Cynthia Guay

Hello,

i want to count the days between my machine is leaving and coming back.

i got a column with my departure date and coming back date. i'm able to count that easy one :) … where it's getting complicated is when i want to ''freeze'' ( and keep )the result of it when it's back or sold, but not freezing the count for other machine that are not back or not sold ( i got a column check box for sold machine) .

right now i got 2 columns to count the days : 1 for current machine out and another counting the days of the one who has come back for stats. and i want to murge them

Question : would i be able to put them in only one formula, to get only one column and keep the number of days for the current and the one who came back or been sold.

If yes, can you help with the formula please.

right now i got this. The part missing is to keep/freeze the result instead of deleting it. : =IF(OR(NOT(ISBLANK([Returning date]@row)), [Sold]@row = 1, NOT(ISBLANK([D.report Photo ]@row))), "", NETDAYS([Departure date]@row, TODAY()))

thanks

have a good day :)

Paul Newcome

@Cynthia Guay Give this a try:

=IF([Returning Date]@row <> "", [Returning Date]@row, TODAY()) - [Departure Date]@row

If you want to keep the existing columns but still want to remove that error message when there is no Returning Date, you could use an IF or an IFERROR.

=IF([Returning Date]@row <> "", returing_date_formula)

=IFERROR(returing_date_formula, "")

Paul Newcome

Are you able to provide a screenshot for context? What are the two working formulas?

Andrée Starå

https://community.smartsheet.com/discussion/127441/freezing-a-result

Hi,

I hope you're well and safe!

This might help!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Paul Newcome

https://community.smartsheet.com/discussion/comment/445137#Comment_445137

I think this is going to be overkill / way more than is needed. There is already a column calculating how many days it is out based on TODAY and a second column calculating the days between the two dates. All the OP needs is help combining the two formulas to capture the number of days in a single column.

Most likely what will be needed is something along the lines of

=IF([Returning Date]@row <> "", [Returning Date]@row, TODAY()) - [Departure Date]@row

Cynthia Guay

@Paul Newcome

Formula in column ''#day demo sold or back'' :

=NETDAYS([Departure date]@row, [Returning date]@row)

(ho btw do you know how i can avoid the msg ''#invalid data type '' if i don't have departure date )

Formula in column # day current demo:

=IF(OR(NOT(ISBLANK([Returning date]@row)), sold@row = 1, NOT(ISBLANK([BL Photo RETOUR]@row))), "", NETDAYS([Departure date]@row, TODAY()))

Thanks

Paul Newcome

@Cynthia Guay Give this a try:

=IF([Returning Date]@row <> "", [Returning Date]@row, TODAY()) - [Departure Date]@row

If you want to keep the existing columns but still want to remove that error message when there is no Returning Date, you could use an IF or an IFERROR.

=IF([Returning Date]@row <> "", returing_date_formula)

=IFERROR(returing_date_formula, "")

Cynthia Guay

@Paul Newcome .. thanks , i'll finally keep it more simple and use your first formula.

Paul Newcome

Happy to help. 👍️