Skip to main content

Hello!


I have a formula for a “status field” that keeps tripping me up. I have tried multiple ways and can get parts of it to work, but not all. Undoubtedly I’m a comma or similar away from the solution, but by now I’ve tangled myself so far in my own head that I have to admit defeat and humbly ask for assistance.



I have the following columns that should be considered in the formula:


{End Date} = Formula field


{BiU} = Checkbox field


{Last Shared} = Date field


{Next Share} = Formula field



What I want to achieve is:


If {BiU} is NOT checked = ‘-’


If {BiU} is checked AND TODAY() is after {End Date} = ✅


If {BiU} is checked AND TODAY() is BEFORE {Last Shared} and {End Date} = ☑


If {BiU} is checked AND TODAY() is AFTER {Last Shared}, but BEFORE {End Date} = 🔗



Any and all help would be greatly appreciated.

How does one share a base (in the forum)?



Just by link or?






Airtable: Organize anything you can imagine





Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.
















You shared it! Thank you, this definitely helps to make better sense of what you’re looking for 🙂 The share link you posted does allow anyone to enter and change stuff though, so I would make sure that is a copy of your real base. For future reference, I typically share a “read-only” link and leave the “Allow users to copy data in this base” box checked. This allows someone to make a copy of the base rather than editing the one you have saved. But this is all a matter of preference!



I took a closer look and found an error I made in the formula I gave you for {Next Share} 😦 If { 🔄 Frequens} is “Low”, the previous formula would only work if the days between {Start} and {End} is greater than 7. If it was less than 7, it would error. The following changes it so that it adds 2 days to {Last Shared} (which I think is what you had before). If none of the parameters match, it will also add 2 days. Happy to tweak if needed.



Apologies for the confusion!



IF({Last Shared}, DATETIME_FORMAT(IF(AND(IS_BEFORE(TODAY(), {Start}), FIND("No", {Tickets}) = 0), DATEADD({Last Shared}, 21, 'days'), SWITCH({🔄 Frequens}, "Low", IF(DATETIME_DIFF({End},{Start}) > 7, DATEADD({Last Shared}, 7, 'days'), DATEADD({Last Shared}, 2, 'days')), "High", IF(DATETIME_DIFF({End}, {Start}) > 14, DATEADD({Last Shared}, 14, 'days'), DATEADD({Last Shared}, 7, 'days')), DATEADD({Last Shared}, 2, 'days'))), 'YYYY-MM-DD'))


You shared it! Thank you, this definitely helps to make better sense of what you’re looking for 🙂 The share link you posted does allow anyone to enter and change stuff though, so I would make sure that is a copy of your real base. For future reference, I typically share a “read-only” link and leave the “Allow users to copy data in this base” box checked. This allows someone to make a copy of the base rather than editing the one you have saved. But this is all a matter of preference!



I took a closer look and found an error I made in the formula I gave you for {Next Share} 😦 If { 🔄 Frequens} is “Low”, the previous formula would only work if the days between {Start} and {End} is greater than 7. If it was less than 7, it would error. The following changes it so that it adds 2 days to {Last Shared} (which I think is what you had before). If none of the parameters match, it will also add 2 days. Happy to tweak if needed.



Apologies for the confusion!



IF({Last Shared}, DATETIME_FORMAT(IF(AND(IS_BEFORE(TODAY(), {Start}), FIND("No", {Tickets}) = 0), DATEADD({Last Shared}, 21, 'days'), SWITCH({🔄 Frequens}, "Low", IF(DATETIME_DIFF({End},{Start}) > 7, DATEADD({Last Shared}, 7, 'days'), DATEADD({Last Shared}, 2, 'days')), "High", IF(DATETIME_DIFF({End}, {Start}) > 14, DATEADD({Last Shared}, 14, 'days'), DATEADD({Last Shared}, 7, 'days')), DATEADD({Last Shared}, 2, 'days'))), 'YYYY-MM-DD'))


Thank you! Both for the fix and the explanaition of sharing bases and good practice there. Yes, it’s a copy of a base and I’ve stripped a bunch of tables, info etc, so no worries what happens to it in the futura. The original is safe.



There are two anomalies in the {BiU Share Status - Test Column} column.


I added a checkbox column called anomalies to easier be able to point them out.


The events have started, and are ongoing which should then be ☑ , but they say 🎟


It is ONLY supposed to say 🎟 if TODAY() is before {Start} (and {Tickets} is not ‘No’)



Edited to add: I can see that ‘ 🔗 ’ doesn’t show for those events that are within range and haven’t yet been shared / {Last Shared} (highlighted by the column {Not yet shared}). I need it to fill all eventualities and leave no blanks.


Thank you! Both for the fix and the explanaition of sharing bases and good practice there. Yes, it’s a copy of a base and I’ve stripped a bunch of tables, info etc, so no worries what happens to it in the futura. The original is safe.



There are two anomalies in the {BiU Share Status - Test Column} column.


I added a checkbox column called anomalies to easier be able to point them out.


The events have started, and are ongoing which should then be ☑ , but they say 🎟


It is ONLY supposed to say 🎟 if TODAY() is before {Start} (and {Tickets} is not ‘No’)



Edited to add: I can see that ‘ 🔗 ’ doesn’t show for those events that are within range and haven’t yet been shared / {Last Shared} (highlighted by the column {Not yet shared}). I need it to fill all eventualities and leave no blanks.


Thank you Annica!



I think the following might work… apologies again for all the back and forth - this one is a bit complicated (as I’m sure you know 🙂 )



I rewrote the formula and I took a look at your base - I think some of the anomalies might be caused because there are differences between the {Next Share} field and the {Next Share - Test Column} field. The below formula as-is returned “ 🔗 ”, but when I changed {Next Share} to {Next Share - Test Column}, it returned “ ☑ ” as expected.



The below formula will also return “ 🔗 ” for anything that has not yet been shared.



IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_BEFORE(TODAY(), {Start}), IF(FIND("No",{Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, 'days')), "⏳", "🔗"), "🎟️"), IF({Next Share}, IF(IS_BEFORE(TODAY(),{Next Share}),"☑️", "🔗")))), "-"), "🔗")


Thank you Annica!



I think the following might work… apologies again for all the back and forth - this one is a bit complicated (as I’m sure you know 🙂 )



I rewrote the formula and I took a look at your base - I think some of the anomalies might be caused because there are differences between the {Next Share} field and the {Next Share - Test Column} field. The below formula as-is returned “ 🔗 ”, but when I changed {Next Share} to {Next Share - Test Column}, it returned “ ☑ ” as expected.



The below formula will also return “ 🔗 ” for anything that has not yet been shared.



IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_BEFORE(TODAY(), {Start}), IF(FIND("No",{Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, 'days')), "⏳", "🔗"), "🎟️"), IF({Next Share}, IF(IS_BEFORE(TODAY(),{Next Share}),"☑️", "🔗")))), "-"), "🔗")




No need for apologies, We’re far beyond the point of what I could achieve on my own. I am beyond greatful for all the help I’m getting.





I added one more column, {BiU Share Status - Latest Test Column} and changed to {Next Share - Test Column} like you said in the testbase. There is only one thing left now that I can see after manually checking each event. One event, that I renamed ‘ ❗ Random event name 25 ❗ ’, so it stands out from the others, should return a ‘ ⏳ ’, but now returns a ‘ 🔗 ’. All other are accurate.





Yes, that is because I like to test things in a new column before applying “permanently”. I know I’m not all that advanced in my skills, so I like to have the freedom to test around and not loose any step until I’ve actually gotten things to work. Maybe it’s a bit of a weird quirk. 🙂




I tested this formula on our “Live” base and noticed a few issues that I think are down to me being to up in formula and ending up being unclear. I’m so sorry this all has ended up far more complicated than a simple answer and solved.



🔗 - If an event starts in the next three days OR if TODAY is on or after {Next Share} (Regardless of what value {Tickets} have).



☑ - If an event has started {Start} but TODAY() is before {End Date}


(Regardless of what value {Tickets} have).



✅ - If TODAY() is after {End Date}


(Regardless of what value {Tickets} have).



🎟 - If you can book tickets (so {Tickets}=‘any other value then No’) AND TODAY() is before {Start}



➖ - If {BiU}=‘No’



I hope I was clear and that it helps you understand what I’m after.




I tested this formula on our “Live” base and noticed a few issues that I think are down to me being to up in formula and ending up being unclear. I’m so sorry this all has ended up far more complicated than a simple answer and solved.



🔗 - If an event starts in the next three days OR if TODAY is on or after {Next Share} (Regardless of what value {Tickets} have).



☑ - If an event has started {Start} but TODAY() is before {End Date}


(Regardless of what value {Tickets} have).



✅ - If TODAY() is after {End Date}


(Regardless of what value {Tickets} have).



🎟 - If you can book tickets (so {Tickets}=‘any other value then No’) AND TODAY() is before {Start}



➖ - If {BiU}=‘No’



I hope I was clear and that it helps you understand what I’m after.


Don’t be sorry! This is a complicated one to solve and without knowing much about the use case it makes it a bit more difficult.





Not a weird quirk - I totally do that too! 🙂 Just wanted to make sure we were both testing the formula against the same dates.



Your latest post was very clear - however it doesn’t take into account the “ ⏳ ” from the previous variations - let me know if that needs to be worked back in. The below will also return “ 🔗 ” if {Last Shared} is empty. I went through and checked each one and it looks like each result matches the parameters you laid out in your last post.



IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(), {Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}) = 0, "🎟️")))), "➖"), "🔗")


Don’t be sorry! This is a complicated one to solve and without knowing much about the use case it makes it a bit more difficult.





Not a weird quirk - I totally do that too! 🙂 Just wanted to make sure we were both testing the formula against the same dates.



Your latest post was very clear - however it doesn’t take into account the “ ⏳ ” from the previous variations - let me know if that needs to be worked back in. The below will also return “ 🔗 ” if {Last Shared} is empty. I went through and checked each one and it looks like each result matches the parameters you laid out in your last post.



IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(), {Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}) = 0, "🎟️")))), "➖"), "🔗")


OMG - my brain.



⏳ - if an event has {Tickets}=‘No’ and TODAY() is more than 3 days before {Start}



How could I forget. And I was trying to collect my thoughts and be clear. I knew I was confusing myself, but that’s extreme.


OMG - my brain.



⏳ - if an event has {Tickets}=‘No’ and TODAY() is more than 3 days before {Start}



How could I forget. And I was trying to collect my thoughts and be clear. I knew I was confusing myself, but that’s extreme.


Hahaha no worries at all!



So I added a new column to your practice base, {BiU Share Status - Latest and Greatest}, with the below formula. Fingers crossed that this is the winner! There were a couple places where I thought it might be wrong as it’s returning a 🔗 instead of ⏳ as it did in previous iterations, but then I realized that’s because the {Next Share} date for those records is today.



IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(),{Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}), "⏳", "🎟️")))), "➖"), "🔗")


Hahaha no worries at all!



So I added a new column to your practice base, {BiU Share Status - Latest and Greatest}, with the below formula. Fingers crossed that this is the winner! There were a couple places where I thought it might be wrong as it’s returning a 🔗 instead of ⏳ as it did in previous iterations, but then I realized that’s because the {Next Share} date for those records is today.



IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(),{Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}), "⏳", "🎟️")))), "➖"), "🔗")


There are three instances where the {Ticket}=‘No’ and TODAY() is way more than three days before {Start} and should then be ‘ ⏳ ’, but show ‘ 🔗 ’. I’ve marked the instances in the column “Should be ⏳ ”.



If we can get that to work I’ll be ecstatic. I’ve spotted another thing in all this, but I’ll deal with that on a rainy day, or month (Grins). These are the functions that are needed now and what matters most.


You have no idea how insanely gratful I am for your invaluable help. Thank you!


There are three instances where the {Ticket}=‘No’ and TODAY() is way more than three days before {Start} and should then be ‘ ⏳ ’, but show ‘ 🔗 ’. I’ve marked the instances in the column “Should be ⏳ ”.



If we can get that to work I’ll be ecstatic. I’ve spotted another thing in all this, but I’ll deal with that on a rainy day, or month (Grins). These are the functions that are needed now and what matters most.


You have no idea how insanely gratful I am for your invaluable help. Thank you!


We’ll get this figured out! I think we’re just having some miscommunication on the dates.



For the three records you checked, the first two are showing 🔗 because TODAY() is after {Next Share}. The last one is showing 🔗 because {Last Shared} is blank.



Let me know what should change 🙂


We’ll get this figured out! I think we’re just having some miscommunication on the dates.



For the three records you checked, the first two are showing 🔗 because TODAY() is after {Next Share}. The last one is showing 🔗 because {Last Shared} is blank.



Let me know what should change 🙂


I missed that two had a share date, which they shouldn’t yet so that’s strange, but oh well; I removed them. But anyway. Those three events checked should all show “ ⏳ ” because it’s more than three days until they start and there is no registration or pre-sale of tickets. When I removed the value in {Last Shared} they should have switched over to “ ⏳ ”, but didn’t.



Ok. I’ll try to be clear and not confuse myself so I end up confusing you as well. If I can achieve that then today is a good day. I’ll type out my rainy day, or month, plan as well so I have it clear in my head already now, but PLEASE don’t feel like you have to trouble yourself with it.



➖ = Event should not be shared at all (is not {BiU}=‘Yes’)



⏳ = Not time to share yet. The event is more than three days away and there is no registration or pre-sale of tickets.





(Down the line I also want this to show if the event has {Tickets=‘value other then No’} and TODAY() is before {Tickets released}, but that truly is something for a rainy day, unless you truly feel like tackling that one as well. I sooo don’t expect you to.)





🎟 = If you can book tickets / reserve seat and TODAY() is before {Start}. (This one is also one for a rainy day, see above as it should only show if TODAY is after {Tickets released}).



🔗 = Share event. Two uses







  1. The event has not yet been shared ({Last Shared}=BLANK()) and the {Start} of the event is either three days away or TODAY().







  2. Today() is on or after {Next Share}.







☑ = The event has been shared ({Last Shared}), is still ongoing (after {Start}, but before {End Date}) and TODAY() is before {Next Share}.



✅ = TODAY() is after {End Date}



I think I might have managed, but you have to be the judge of that. Clear? Clear-ish?


I missed that two had a share date, which they shouldn’t yet so that’s strange, but oh well; I removed them. But anyway. Those three events checked should all show “ ⏳ ” because it’s more than three days until they start and there is no registration or pre-sale of tickets. When I removed the value in {Last Shared} they should have switched over to “ ⏳ ”, but didn’t.



Ok. I’ll try to be clear and not confuse myself so I end up confusing you as well. If I can achieve that then today is a good day. I’ll type out my rainy day, or month, plan as well so I have it clear in my head already now, but PLEASE don’t feel like you have to trouble yourself with it.



➖ = Event should not be shared at all (is not {BiU}=‘Yes’)



⏳ = Not time to share yet. The event is more than three days away and there is no registration or pre-sale of tickets.





(Down the line I also want this to show if the event has {Tickets=‘value other then No’} and TODAY() is before {Tickets released}, but that truly is something for a rainy day, unless you truly feel like tackling that one as well. I sooo don’t expect you to.)





🎟 = If you can book tickets / reserve seat and TODAY() is before {Start}. (This one is also one for a rainy day, see above as it should only show if TODAY is after {Tickets released}).



🔗 = Share event. Two uses







  1. The event has not yet been shared ({Last Shared}=BLANK()) and the {Start} of the event is either three days away or TODAY().







  2. Today() is on or after {Next Share}.







☑ = The event has been shared ({Last Shared}), is still ongoing (after {Start}, but before {End Date}) and TODAY() is before {Next Share}.



✅ = TODAY() is after {End Date}



I think I might have managed, but you have to be the judge of that. Clear? Clear-ish?


Hi Annica!



Apologies for the delay in getting back to you… things have been crazy lately!



Thank you so much for the clear explanations. It definitely helps a ton. I want to make sure I have this right before giving you yet another formula that doesn’t work 😦 I’m starting to feel like I’m not helping, lol.



No Share ➖



After Event ✅



During Event


To Be Shared 🔗


Shared ☑



More than 3 Days Before Event


No Tickets ⏳


Tickets 🎟



Less than 3 Days Before Event


To Be Shared 🔗


Tickets 🎟



If all that looks correct, I’ll get to work!


Hi Annica!



Apologies for the delay in getting back to you… things have been crazy lately!



Thank you so much for the clear explanations. It definitely helps a ton. I want to make sure I have this right before giving you yet another formula that doesn’t work 😦 I’m starting to feel like I’m not helping, lol.



No Share ➖



After Event ✅



During Event


To Be Shared 🔗


Shared ☑



More than 3 Days Before Event


No Tickets ⏳


Tickets 🎟



Less than 3 Days Before Event


To Be Shared 🔗


Tickets 🎟



If all that looks correct, I’ll get to work!


That looks correct to me. Below is just me trying to be super clear to avoid confusion.



During event


☑ IF {Last Shared} AND before {Next Share}



Less than 3 Days Before Event


To Be Shared 🔗


Tickets 🎟



**On day of event / {Start} **


To Be Shared 🔗


Tickets 🔗


That looks correct to me. Below is just me trying to be super clear to avoid confusion.



During event


☑ IF {Last Shared} AND before {Next Share}



Less than 3 Days Before Event


To Be Shared 🔗


Tickets 🎟



**On day of event / {Start} **


To Be Shared 🔗


Tickets 🔗


Excellent - thank you again!



I’m very anxious to see if this works. I added another column to your base - {BiU Share Status - Newest}. I checked and it looks like all the parameters are met… let me know 🙂



IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(OR(IS_SAME(TODAY(), {Start}), IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days'))), IF({Last Shared}, IF(IS_AFTER(TODAY(), {Next Share - Test Column}), "🔗", "☑️"), "🔗"), IF(IS_BEFORE(TODAY(), {Start}), IF(FIND("No", {Tickets}), "⏳", "🎟️")))), "➖")


Excellent - thank you again!



I’m very anxious to see if this works. I added another column to your base - {BiU Share Status - Newest}. I checked and it looks like all the parameters are met… let me know 🙂



IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(OR(IS_SAME(TODAY(), {Start}), IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days'))), IF({Last Shared}, IF(IS_AFTER(TODAY(), {Next Share - Test Column}), "🔗", "☑️"), "🔗"), IF(IS_BEFORE(TODAY(), {Start}), IF(FIND("No", {Tickets}), "⏳", "🎟️")))), "➖")


We have a WINNER!!!



Yay!, You’re amazing.


Thank you sooo much for spending so much time on this and helping even when I confuse myself.


I don’t know how I’ll ever repay you, but if nothing else I’ll try to pay it forward.


We have a WINNER!!!



Yay!, You’re amazing.


Thank you sooo much for spending so much time on this and helping even when I confuse myself.


I don’t know how I’ll ever repay you, but if nothing else I’ll try to pay it forward.


Oh my goodness, yay!! You’re so welcome, and thank you for taking the time to explain in detail - that’s what really did the trick. I was getting confused on what dates would be expected to fall in what order, so your final explanation was extraordinarily helpful toward correcting and simplifying the formula.



Good luck and don’t hesitate to shoot me a message if you need help in the future! 🙂


Hahaha no worries at all!



So I added a new column to your practice base, {BiU Share Status - Latest and Greatest}, with the below formula. Fingers crossed that this is the winner! There were a couple places where I thought it might be wrong as it’s returning a 🔗 instead of ⏳ as it did in previous iterations, but then I realized that’s because the {Next Share} date for those records is today.



IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(),{Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}), "⏳", "🎟️")))), "➖"), "🔗")


Hello, @AlliAlosa !


Please help me with my issue.


I’m gonna filter records on this:


$filtered_row = $airtable->getContent( ‘Invoiced’, g‘filterByFormula’ => “IS_SAME({LastModifiedTime}, ‘. $today_date .’)”]);



In other words, I’m gonna compare the “LastModifiedTime” field in record with today date.



How can I do this?


I have something similar but not quite as complicated, but I’m still struggling LOL



I need the following:


If Phase/Status is Cancelled or Complete AND Last Modified is more than 14 days ago, formula field should read “Remove From Digest”, otherwise, BLANK.



Use case: I’m sending an automated email of our project pipeline and if something is cancelled/Complete and we don’t update the record for 2 weeks, we want it to drop off the view that is generating the email if that makes sense. I.e. we are only seeing cancelled/complete projects for a certain amount of time and not for the life time of our airtable use.


I have something similar but not quite as complicated, but I’m still struggling LOL



I need the following:


If Phase/Status is Cancelled or Complete AND Last Modified is more than 14 days ago, formula field should read “Remove From Digest”, otherwise, BLANK.



Use case: I’m sending an automated email of our project pipeline and if something is cancelled/Complete and we don’t update the record for 2 weeks, we want it to drop off the view that is generating the email if that makes sense. I.e. we are only seeing cancelled/complete projects for a certain amount of time and not for the life time of our airtable use.


Let’s divide this into chunks…





This could be done a couple different ways. The first option that many will attempt would be something like this:



OR({Phase/Status} = "Cancelled", {Phase/Status} = "Complete")



However, it could also be done this way:



REGEX_MATCH({Phase/Status}, "Cancelled|Complete")



I’ll use the latter option going forward, largely because it’s shorter.



Next chunk:





For this we’ll compare LAST_MODIFIED_TIME() against NOW() using DATETIME_DIFF():



DATETIME_DIFF(NOW(), LAST_MODIFIED_TIME(), "days") > 14



Each of those expressions will return True or False. To test if they’re both true, we can combine their logic using the AND() function:



AND(

REGEX_MATCH({Phase/Status}, "Cancelled|Complete"),

DATETIME_DIFF(NOW(), LAST_MODIFIED_TIME(), "days") > 14

)



Finally we’ll wrap that into an IF() function to create the desired output if both are True:



IF(

AND(

REGEX_MATCH({Phase/Status}, "Cancelled|Complete"),

DATETIME_DIFF(NOW(), LAST_MODIFIED_TIME(), "days") > 14

),

"Remove From Digest"

)



While some might be tempted to add the BLANK() function as a third argument in the IF() function, it’s not necessary. The IF() function is already designed to not return anything if that argument is omitted.


Reply