列公式不显示在甘特图上

Sahand
Sahand ✭
在公式和函数

大家好！

我有一列由“到期日期”和另一组被称为“延期日期”的日期组成。我在“到期日”一栏中添加了公式“=IF([Extension Approval Status]@row = "ENABLED"， [Extension Date]@row)”，如果已经进行了延期并且已经获得批准，那么“延期日”将取代“到期日”。

但是，当将公式输入到转换后的列公式中时，它会去掉所有以前的到期日期，并且不允许我在甘特图上选择“到期日期”。它也不允许在提交新表单时在网格中记录新的到期日期。

这是一个错误还是有一个解释这种行为?

有解决的可能吗?

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

答案

  • 乔有关的
    乔有关的 ✭✭✭✭✭✭

    嗨Sahand !

    只是为了检查，我假设列都是“日期”列正确吗?

    此外，您是否验证了“项目设置”中的所有设置?

    • 乔有关的
    · 在Facebook上分享 在Twitter上分享
  • Sahand
    Sahand ✭

    嗨@Joe有关的

    谢谢你的回复，

    是的，这两列都是日期，除了“过期批准状态”，它有一个下拉选项接受，拒绝或待处理。

    你指的是项目设置中的哪些部分?


    非常感谢,

    Sahand

    · 在Facebook上分享 在Twitter上分享
  • 保罗。麦吉尼斯
    保罗。麦吉尼斯 ✭✭✭✭✭✭

    嗨@Sahand

    抱歉，只是根据你提供的描述，如果过期日期列中的公式是列公式，如果不将其更改为单元格公式，则不允许您手动修改/过键入。

    通常，您需要单独的列来包含您需要在公式中引用或拉回列公式的任何数据/信息。

    下面的红色列是一个列式公式，只能运行公式，所以你需要在其他列中进行修改，这样公式就可以拉出你想要的信息。

    image.png

    主日期列中的公式为

    =IF([延期批准状态]@row = "ENABLED"，[延期日期]@row，[到期日期]@row)

    使用列公式时要记住的一些东西，希望能有所帮助

    如果这与您的问题无关，请道歉，它似乎只是由描述。

    谢谢

    保罗

    · 在Facebook上分享 在Twitter上分享

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
\n \n https:\/\/community.smartsheet.com\/discussion\/comment\/379822#Comment_379822\n <\/a>\n<\/div>\n

The problem I was having with something similar to what you have is that it wasn't picking up projects that started before the quarter and ended after the quarter. So if project started in 2022Q4, continued in 2023Q1, and ended during 2023Q2, I would want a \"1\" for all three quarters but it was only giving me one for 2022Q4 and 2023Q2 (skipping 2023Q1).<\/p>

I actually just got this working late last night (with the help of ChatGPT). Here is what I ended up with. This one checks for 2022Q1.<\/p>

=IF(OR(AND($Start@row <= DATE(2022, 3, 31), $End@row >= DATE(2022, 1, 1)), AND(DATE(2022, 1, 1) <= $End@row, DATE(2022, 3, 31) >= $Start@row)), 1, 0)<\/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":106224,"type":"question","name":"Simple Sum question","excerpt":"This is a simple one but just having a hard time getting it to work. I have multiple clients who purchase a variety of products per invoice. I need a Sum of the Product Type per Client. I've attached an image. For example, I'd like to know Sum of Product 1 for Client 1. In this case the running total =5. I realize I could…","categoryID":322,"dateInserted":"2023-06-08T18:35:50+00:00","dateUpdated":"2023-06-08T18:41:54+00:00","dateLastComment":"2023-06-09T14:01:57+00:00","insertUserID":152073,"insertUser":{"userID":152073,"name":"jedelman","url":"https:\/\/community.smartsheet.com\/profile\/jedelman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T13:59:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":152073,"lastUser":{"userID":152073,"name":"jedelman","url":"https:\/\/community.smartsheet.com\/profile\/jedelman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T13:59:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":19,"score":null,"hot":3372569867,"url":"https:\/\/community.smartsheet.com\/discussion\/106224\/simple-sum-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106224\/simple-sum-question","format":"Rich","tagIDs":[254,334],"lastPost":{"discussionID":106224,"commentID":379823,"name":"Re: Simple Sum question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379823#Comment_379823","dateInserted":"2023-06-09T14:01:57+00:00","insertUserID":152073,"insertUser":{"userID":152073,"name":"jedelman","url":"https:\/\/community.smartsheet.com\/profile\/jedelman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T13:59:15+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:01:33+00:00","dateAnswered":"2023-06-08T18:45:45+00:00","acceptedAnswers":[{"commentID":379689,"body":"

Hi @jedelman<\/a> <\/p>

This formula should work:<\/p>

=SUMIFS([Total Per Invoice]:[Total Per Invoice], Client:Client, \"Client 1\", [Product\/Item]:[Product\/Item], \"Product 1\")<\/p>

If you are putting it on a metric sheet, the ranges will look a little different and you could save time by listing the clients in a column and the products in another column and using the \"@row\" function instead of the typing out \"Client 1\", for example. Then you could drag the formula down or make it a column formula depending on your use case.<\/p>

I hope that helps.<\/p>

Matt<\/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":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":106203,"type":"question","name":"Quick CountIfsquery Please","excerpt":"Hello, Hoping somebody can make my life easier please. I'm currently using the following countifs formula: =COUNTIFS({CoCode}, \"114\", {Status.}, \"Not Started\") What can I change it to so I don't have to manually enter a new co. code(eg 114) and status (eg Not Started) every time? Imagine there is some way I can just tell…","categoryID":322,"dateInserted":"2023-06-08T15:16:32+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T13:47:45+00:00","insertUserID":157985,"insertUser":{"userID":157985,"name":"David G11","url":"https:\/\/community.smartsheet.com\/profile\/David%20G11","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T10:12:53+00:00","banned":0,"punished":0,"private":true,"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-09T14:12:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":24,"score":null,"hot":3372558257,"url":"https:\/\/community.smartsheet.com\/discussion\/106203\/quick-countifsquery-please","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106203\/quick-countifsquery-please","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106203,"commentID":379820,"name":"Re: Quick CountIfsquery Please","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379820#Comment_379820","dateInserted":"2023-06-09T13:47:45+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-09T14:12:17+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\/NKBOH1CBAHGH\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-08T22:45:43+00:00","dateAnswered":"2023-06-08T15:43:59+00:00","acceptedAnswers":[{"commentID":379641,"body":"

You would use somethign along the lines of:<\/p>

=COUNTIFS({CoCode}, $[Column7]@row, {Status.}, [Column8]$15)<\/p>


<\/p>

Just change the 15 after [Column8]$ to whatever row number has that text in it. Then you can dragfill down and to the right.<\/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"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 检查项目的开始/结束日期是否在特定的时间范围内。

    我的团队通常需要6-12个月才能完成项目。我想创建一个汇总表，以确定项目在哪个季度进行。我正在为每个项目创建一个新行，并从每个项目的表中链接开始和结束日期。然后我为每个季度创建了一个新专栏……

  • 简单和题

    这是一个简单的方法，只是很难让它工作。我有很多客户，他们每张发票都会购买各种各样的产品。ob欧宝娱乐app手机下载我需要每个客户的产品类型的总和。ob欧宝娱乐app手机下载我附上了一张图片。例如，我想知道客户1的Product 1的Sum。ob欧宝娱乐app手机下载在本例中，运行总数=5。我意识到我可以……

  • 快速计数查询

    你好，希望有人能让我的生活更轻松。我目前使用以下countifs公式:= countifs ({CoCode}，“114”，{状态。}，“未启动”)我可以将其更改为什么，这样我就不必每次都手动输入新的co代码(例如114)和状态(例如未启动)?想象一下，有一种方法可以让我知道……