在某一日期之后

JAA
JAA ✭
编辑12/09/19 在公式和函数

嗨！

我试图创建一个公式，该公式将计算2019年1月1日之后具有“交付日期”的行数。任何日期在此之前的行都不应被计算在内。我也不希望空白行计数。

在附带的屏幕截图中，您将看到除了一行之外的所有填充行都应该被计数。

有什么公式我可以用的吗?感谢您提供的任何帮助!

杰克

2019 - 03 - 29 - _11 30 - 11. jpg

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

评论

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
30, {011-AP Archive -# of Days to Complete}, <60) I was able to do this same formula with a COUNTIFS but its' not working the same, what am I missing.","categoryID":322,"dateInserted":"2023-06-16T15:00:58+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156010,"lastUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3373857824,"url":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","format":"Rich","lastPost":{"discussionID":106550,"commentID":381027,"name":"Re: Invalid Operation","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381027#Comment_381027","dateInserted":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+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-16T15:22:31+00:00","dateAnswered":"2023-06-16T15:19:44+00:00","acceptedAnswers":[{"commentID":381026,"body":"

Try this:<\/p>

SUMIFS({011-AP Archive -# of Days to Complete}, {011-AP Archive -# of Days to Complete}, > 30, {011-AP Archive -# of Days to Complete}, < 60)<\/p>

With SUMIFS, you have to include the range to SUM separately from the ranges to compare. In your scenario, they are the same, but can be different.<\/p>

SUMIFS( range, criterion_range1, criterion1, [ criterion_range2, ​criterion2​... ])<\/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":106528,"type":"question","name":"Formula Calculation with a zero value denominator","excerpt":"I'm trying to create a formula to calculate the # Hours per remaining week based off the total HOURS remaining. Calculation takes into consideration the calculated remaining # of weeks on a project. Logic should be: Take Remaining hours \/ # of remaining weeks. If remaining weeks is < 1, simply return the amount of…","categoryID":322,"dateInserted":"2023-06-15T20:54:01+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T16:47:02+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161931,"lastUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":19,"score":null,"hot":3373797663,"url":"https:\/\/community.smartsheet.com\/discussion\/106528\/formula-calculation-with-a-zero-value-denominator","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106528\/formula-calculation-with-a-zero-value-denominator","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106528,"commentID":381042,"name":"Re: Formula Calculation with a zero value denominator","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381042#Comment_381042","dateInserted":"2023-06-16T16:47:02+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/09DN1EF6F1P9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-16T16:47:11+00:00","dateAnswered":"2023-06-16T11:42:38+00:00","acceptedAnswers":[{"commentID":380991,"body":"

Hi @Christine Cao<\/a>,<\/p>

It's down to your formula and rounding. What's happening is that you have 0.3 of a week (which I'm guessing is actually 2 days - 0.2857..., rounded to 0.3). If you calculate 321.5\/(2\/7), you end up with the 1125.3 (to 1 dp).<\/p>

You can get round this by altering your formula to do what you're actually after:<\/p>

=IF([Column2]9 >= 1, [Column3]@row \/ [Column2]9, MAX([Column3]@row, 0))<\/p>

This should make it so if weeks remaining is equal to or greater than 1 it will do the calculation, and otherwise take the MAX of either 0 or column 3.<\/p>

Give this a try and hopefully it should resolve your issue!<\/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":106512,"type":"question","name":"Mandatory \"pick value from list\" field in Global Update","excerpt":"Issue with the Global Update feature, specifically when trying to add a new value to an existing column without altering any existing values. Here's the situation: Objective: Add the value \"Cancelled\" to the \"Status\" column in the Project Plan. Approach: Global Update functionality to ensure this change is reflected across…","categoryID":322,"dateInserted":"2023-06-15T17:51:44+00:00","dateUpdated":"2023-06-15T18:13:35+00:00","dateLastComment":"2023-06-16T11:23:17+00:00","insertUserID":151950,"insertUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":151950,"lastUserID":151950,"lastUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3373767301,"url":"https:\/\/community.smartsheet.com\/discussion\/106512\/mandatory-pick-value-from-list-field-in-global-update","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106512\/mandatory-pick-value-from-list-field-in-global-update","format":"Rich","lastPost":{"discussionID":106512,"commentID":380989,"name":"Re: Mandatory \"pick value from list\" field in Global Update","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380989#Comment_380989","dateInserted":"2023-06-16T11:23:17+00:00","insertUserID":151950,"insertUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+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-16T11:23:58+00:00","dateAnswered":"2023-06-15T23:55:36+00:00","acceptedAnswers":[{"commentID":380951,"body":"

Hi Mirelle,<\/p>


<\/p>

When applying the global update to modify the column, right under the selection of the column name, there are two checkboxes. One to change column property and the other is to change column cell data. Both are checked by default. To ensure only the new dropdown values are pushed and no existing values are updated, all you have to do it uncheck the box which says Change column cell data. This will remove the Pick value from list option altogether and you can apply the update.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&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 in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 无效的操作

    早上好，我正在尝试求和大于30但小于60天的条目。=SUMIFS({011-AP Archive -# of Days to Complete}， >30， {011-AP Archive -# of Days to Complete}， <60)我能够用COUNTIFS做同样的公式，但它的工作方式不一样，我错过了什么。

  • 公式以零值分母计算

    我试图创建一个公式来计算基于剩余总小时数的每周剩余小时数。计算将考虑计算出的项目剩余周数。逻辑应该是:取剩余时间/剩余周数。如果剩余周数< 1，则返回…

  • 全局更新中必选的“从列表中选择值”字段

    Global Update特性的问题，特别是在尝试向现有列添加新值而不更改任何现有值时。具体情况如下:目标:在项目计划的“状态”栏中添加“取消”值。方法:全局更新功能，以确保此更改反映在…