Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.

Automatically uncheck column based on date TODAY()

Charlie Shike
Charlie Shike ✭
edited 12/09/19 inArchived 2015 Posts

How can I get columns unchecked automatically when they meet a date criterion? For example, all tasks checked today will be automatically unchecked tomorrow because the calendar date has changed.

Tags:
· Share on Facebook Share on Twitter

Comments

  • John Sauber
    John Sauber ✭✭✭✭✭✭

    I think we need some more detail here. It sounds like you're asking to uncheck everything which is checked in a given column at midnight, every day.

    · Share on Facebook Share on Twitter
  • Travis
    Travis Employee

    I agree with john, could we get a little more detail about what you are looking for? It may be helpful to hear your use case for this functionality

    · Share on Facebook Share on Twitter
  • Charlie Shike
    Charlie Shike ✭

    John, Travis - Thanks for your request for clarification. Yes, at midnight I would like all checked items in a column automatically unchecked.

    · Share on Facebook Share on Twitter
  • John Sauber
    John Sauber ✭✭✭✭✭✭
    edited 06/17/15

    I can't think of a way to get this done. Try changing your proecessTongue Out!

    · Share on Facebook Share on Twitter
  • Charlie Shike
    Charlie Shike ✭
    edited 06/17/15

    I'll wait to hear from others. I hope there's a solution on the horizon ...

    · Share on Facebook Share on Twitter
  • Travis
    Travis Employee
    edited 06/18/15

    查理,你可以有一个公式,如果托德说ay is a certain date, uncheck the box, then at midnight they will uncheck, but it sounds like you want them to uncheck every night at midnight, correct? I would just open your sheet, click on the column header and hit the backspace key on your keyboard. This will uncheck all the boxes in that column and takes two clicks.

    · Share on Facebook Share on Twitter
  • Charlie Shike
    Charlie Shike ✭

    Travis - the backspace on header solution works like a charm! This gives me time to ponder my formula now. Many thanks!

    · Share on Facebook Share on Twitter
  • Oby
    Oby ✭✭✭

    I have a similar situation with a monthly checklist I'm assigning.

    Each month, I want the check box to "reset" (as in uncheck) so the assignee goes through the same steps by checking boxes to confirm everything is in stock.

    I can add a formula to do this but the issue is, when someone checks the box, it erases the formula!

    Anyone have a workaround for this?

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

Hi Candace,<\/p>

The answer is YES. You can use \"Send as Attachment\" feature in the File menu of your sheet to setup the schedule for your need. You should be a licensed user to use this feature as well (I remembered that). <\/p>

Hope this works for you.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":268,"urlcode":"Attachments","name":"Attachments"},{"tagID":300,"urlcode":"Email Notifications","name":"Email Notifications"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"}]},{"discussionID":108226,"type":"question","name":"COUNTIF and OR or IF Formula","excerpt":"I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like this: =COUNTIFS({Despatch Report Date}, =Date8, {Despatch Report Lot Status}, \"Q\", {Despatch Report WO Status}, <99 I basically want to count if its…","snippet":"I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like…","categoryID":322,"dateInserted":"2023-07-27T21:30:27+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T22:39:47+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T22:59:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156297,"lastUser":{"userID":156297,"name":"Pat Canning","url":"https:\/\/community.smartsheet.com\/profile\/Pat%20Canning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5XYNMD97JXRY\/nD28D8BFM2BGU.jpg","dateLastActive":"2023-07-27T23:12:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":30,"score":null,"hot":3380991614,"url":"https:\/\/community.smartsheet.com\/discussion\/108226\/countif-and-or-or-if-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108226\/countif-and-or-or-if-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108226,"commentID":387692,"name":"Re: COUNTIF and OR or IF Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387692#Comment_387692","dateInserted":"2023-07-27T22:39:47+00:00","insertUserID":156297,"insertUser":{"userID":156297,"name":"Pat Canning","url":"https:\/\/community.smartsheet.com\/profile\/Pat%20Canning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5XYNMD97JXRY\/nD28D8BFM2BGU.jpg","dateLastActive":"2023-07-27T23:12:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T22:59:50+00:00","dateAnswered":"2023-07-27T22:39:47+00:00","acceptedAnswers":[{"commentID":387692,"body":"

Does this work?<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108213,"type":"question","name":"How to spread costs evenly across months, if they fall within those dates","excerpt":"I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various functions already, IF, AND, and it says Unpearsable. I'd like to spread out the costs in the Cost column to the months on the right, based on the start…","snippet":"I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various…","categoryID":321,"dateInserted":"2023-07-27T19:03:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:36:58+00:00","insertUserID":164209,"insertUser":{"userID":164209,"name":"AshwiniBiotech2023","title":"Sr. Director, Project Management","url":"https:\/\/community.smartsheet.com\/profile\/AshwiniBiotech2023","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T19:37:39+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T04:02:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":33,"score":null,"hot":3380975440,"url":"https:\/\/community.smartsheet.com\/discussion\/108213\/how-to-spread-costs-evenly-across-months-if-they-fall-within-those-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108213\/how-to-spread-costs-evenly-across-months-if-they-fall-within-those-dates","format":"Rich","lastPost":{"discussionID":108213,"commentID":387659,"name":"Re: How to spread costs evenly across months, if they fall within those dates","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387659#Comment_387659","dateInserted":"2023-07-27T19:36:58+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T04:02:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/IBAVVE3YLO8D\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T09:38:32+00:00","dateAnswered":"2023-07-27T19:21:12+00:00","acceptedAnswers":[{"commentID":387646,"body":"

You would use the below for Jan 2023 and adjust the month and year numbers accordingly for each of the other months.<\/p>

=IF(AND(MONTH([Start Date]@row)<= 1, YEAR([Start Date]@row)<= 2023, MONTH([End Date]@row)>= 1, YEAR([End Date]@row)>= 2023), [$ per month]@row)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • Is an automated export of a Smartsheet to an Excel file possible?

    Hi all, I have been having the most frustrating conversation with Smartsheet support on this topic and not getting answers to the question that I asked. I would like to set up an automation that will email me an Excel copy of my project plans on a weekly basis (or when triggered with a change - I don't really care) as I…

  • COUNTIF and OR or IF Formula

    I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like this: =COUNTIFS({Despatch Report Date}, =Date8, {Despatch Report Lot Status}, "Q", {Despatch Report WO Status}, <99 I basically want to count if its…

  • How to spread costs evenly across months, if they fall within those dates

    I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various functions already, IF, AND, and it says Unpearsable. I'd like to spread out the costs in the Cost column to the months on the right, based on the start…