我们的产品有过期ob欧宝娱乐app手机下载日期，我们需要一个细胞来计算它们在几天内的数量。

Ian777
Ian777 ✭
在公式和函数

我们有一个带有截止日期的列，它是由员工在收到其他数据时手动输入的。目前，我们有条件格式突出显示的产品在30天内通过行变成黄色。ob欧宝娱乐app手机下载我们需要一个单元格，将自动计数和显示所有黄色(30天内到期)的值，并忽略已经过期(红色)的产品。ob欧宝娱乐app手机下载能做到这一点或类似的事情吗?提前感谢您的任何帮助。

标签:
· 在Facebook上分享 在Twitter上分享

答案

  • 戴尔·墨菲
    戴尔·墨菲 ✭✭✭✭✭✭

    @Ian777我的方法是添加一个列，用于计算到期天数。

    它将是一个列式公式，如下所示=[到期日]@row -今天()

    然后数一下有多少个[距离过期天数]大于0小于30。喜欢的东西:

    =COUNTIFS([Days to Expire]:[Days to Expire]， >-1， [Days to Expire]:[Days to Expire]， <30)

    你打算把结果放在哪里?你是否有一个仪表盘来保存这个指标?也许构建一个摘要字段(这就是公式的来源)并在报告中使用它?

    dm

    · 在Facebook上分享 在Twitter上分享
  • Ian777
    Ian777 ✭
    SS Help 1.png

    非常感谢你的帮助。这里是一个没有任何敏感信息的项目示例的屏幕截图。

    “特定位置数量”栏中的蓝色单元格汇总了每个房间具体有多少材料。这工作得很好，为了做到这一点，我使用了以下内容。

    =SUMIF(位置:位置，房间号，([完成数量/数量]:[完成数量/数量]))

    “Complete Count/QTY”列中的蓝色单元格使用以下代码对整个列的总数进行求和。

    =SUM([完整数量/数量]:[完整数量/数量])

    我一直无法弄清楚的是如何创建一个单元格，它不仅会计算有多少行材料将在30天内到期，而且还会计算所有行将在30天内到期的行的“完整计数/数量”之和。

    例如，使用上面的图像，单元格将理想地告诉我们7而不是1，表示材料过期的那一行。任何帮助都将非常感激。提前谢谢你。

    · 在Facebook上分享 在Twitter上分享
  • Ian777
    Ian777 ✭

    这是我最接近的一次了。不幸的是，它不是从今天到30天的总和，而是在特定月份的总和。

    =SUMIF([Expiration Date]:[Expiration Date]， (IFERROR(MONTH(@cell)， 0) = 2)， [Complete Count/QTY]:[Complete Count/QTY])

    此外，即使这样做，我们也必须为每个月创建一个单元格，这对于工作表来说有点麻烦。

    旁注:我只学了一个星期的Smartsheet，所以我还有很多东西要学。

    · 在Facebook上分享 在Twitter上分享
  • Ian777
    Ian777 ✭

    对于任何需要这个相同功能的人来说，它已经解决了!

    =SUMIF([Expiration Date]:[Expiration Date]， (IFERROR(MONTH(@cell)， 0) = 2)， [Complete Count/QTY]:[Complete Count/QTY])

    · 在Facebook上分享 在Twitter上分享
  • Ian777
    Ian777 ✭

    更正-我抄错了公式

    =SUMIFS([完整计数/数量]:[完整计数/数量]，[截止日期]:[截止日期]，[截止日期]@row <= TODAY() + 30，[截止日期]:[截止日期]，[截止日期]@row >= TODAY())

    · 在Facebook上分享 在Twitter上分享

帮助文章参考资料欧宝体育app官方888

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
@CamSME<\/a> you'll use join and collect with children or decedents as the range. This video might help.<\/p>
\n \n https:\/\/youtu.be\/Dzo0UYjxMmI\n <\/a>\n<\/div>


<\/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":[]},{"discussionID":106286,"type":"question","name":"Health Bubble Turn Colors when Status is Updated","excerpt":"Hello, I am new to Smartsheet. I am trying to get when I change my Status to \"Not Started\" I want the Health bubble to automatically turn grey. And when I change my Status to \"In Progress\" the Health bubble will turn yellow, etc. So far I have: IF(Status1 = \"Not Started\", \"Gray\", \" \"). I want it to change color for each…","categoryID":322,"dateInserted":"2023-06-09T19:29:22+00:00","dateUpdated":"2023-06-09T20:02:36+00:00","dateLastComment":"2023-06-09T21:14:52+00:00","insertUserID":162263,"insertUser":{"userID":162263,"name":"clairehunter","url":"https:\/\/community.smartsheet.com\/profile\/clairehunter","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-10T18:54:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162263,"lastUserID":150413,"lastUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":26,"score":null,"hot":3372684854,"url":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","format":"Rich","tagIDs":[254,440],"lastPost":{"discussionID":106286,"commentID":379933,"name":"Re: Health Bubble Turn Colors when Status is Updated","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379933#Comment_379933","dateInserted":"2023-06-09T21:14:52+00:00","insertUserID":150413,"insertUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-10T18:54:41+00:00","dateAnswered":"2023-06-09T21:14:52+00:00","acceptedAnswers":[{"commentID":379933,"body":"

You will need a nested IF formula:<\/p>

=IF(Status@row=\"Not Started\", \"Gray\", IF(Status@row=\"In Progress\", \"Yellow\", IF(Status@row=\"Complete\", \"Green\")))<\/p>


<\/p>

Just add more IF Statements like above for what you need. Put all ) at the very end. Then you can turn it into a column formula by right clicking the cell the formula is in and selecting column formula.<\/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"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]},{"discussionID":106265,"type":"question","name":"If Date is blank + past formula","excerpt":"I can't figure out why this would return a \"yes\" when the date cell is blank. =IF([Date of Site Survey (Product)]@row = \" \", \" \", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \" \")) Intended logic\/result - If date cell is blank, then \"blank\" (this worked when I tried without the 2nd argument added) If date is in…","categoryID":322,"dateInserted":"2023-06-09T14:00:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T23:14:36+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T20:05:00+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-06-10T20:34:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3372673501,"url":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","format":"Rich","lastPost":{"discussionID":106265,"commentID":379953,"name":"Re: If Date is blank + past formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379953#Comment_379953","dateInserted":"2023-06-09T23:14:36+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-06-10T20:34:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-09T14:28:35+00:00","dateAnswered":"2023-06-09T14:12:11+00:00","acceptedAnswers":[{"commentID":379830,"body":"

Try this:<\/p>

=IF([Date of Site Survey (Product)]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \"no\"))<\/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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 遇到了父单元格聚合子任务名称的公式。

    我想捕获单个项目名称，由子任务跟踪，并将它们聚合在父单元格(所有项目名称)中。这样，当最小化时，我就不必展开来查看任务是针对哪些站点。同样对于Dashboard视图，查看哪些站点是被跟踪的父项目的一部分。截图……

  • 当状态更新时，生命泡泡会变颜色

    你好，我是Smartsheet的新手。我试图得到当我改变我的状态为“未开始”，我想要的健康气泡自动变成灰色。当我将自己的状态更改为“进行中”时，生命值气泡将变成黄色，等等。到目前为止，我有:IF(Status1 = "Not Started"， "Gray"， " ")。我想让它改变颜色…

  • 如果Date是空白+过去公式

    我不明白为什么这将返回一个“是”当日期单元格是空白。=IF([现场调查(产品)日期]@row = " "ob欧宝娱乐app手机下载， " "， IF(今天()>[现场调查(产品)日期]@row， "是"，" ")预期的逻辑/结果-如果日期单元格为空白，则"空白"(当我尝试不添加第二个参数时有效)如果日期在…