如何以小时计算细胞的年龄

wtcf
wtcf ✭
编辑10/26/21 在公式和函数

背景:

我有一个表单，它在网格中作为一行输出，其摘录如下-

image.png

“日期了'列包含系统生成-创建(日期)值和'当前日期/时间'列包含今天的日期(使用=今天()功能)。

问题,

我试图找到一个公式驱动的解决方案，这将填充“任务年龄(小时)”列在“日期提出”和“当前日期/时间”的值之间的小时数的差异，即时间经过的小时数。

这样做的目的是跟踪任务是否按时完成，例如高优先级(4个业务小时)，中等优先级(8个业务小时)和低优先级(24个业务小时)。

在我看来，我有以下障碍需要克服

  • 当前日期/时间列-是否有公式(例如可以展开的东西)=今天())将以与“提出日期”列相同的格式填充日期和时间?
  • 任务的年龄列-如果上述是可能的，那么我将如何计算“提出日期”和“当前日期/时间”列之间的差异(以小时为单位)。


提前感谢您，任何人都可以提供任何帮助。

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

答案

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
Formulas don't always scale very well which is why I suggested a reference table. If you would prefer to work with a formula, then you would use something like this for the example in your original post:<\/p>

=IF(CONTAINS(\"Premium\", [Which newsletter?]@row), IF(CONTAINS(\"Spotlight\", Placement@row), \"1000\", \"900\"), IF(CONTAINS(\"Spotlight\", Placement@row), \"800\", \"700\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-13 20:28:39","updateUser":{"userID":156854,"name":"Alyce R.","url":"https:\/\/community.smartsheet.com\/profile\/Alyce%20R.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T22:37:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":99675,"type":"question","name":"Tracking the cumulative days of a cell value change","excerpt":"I need to create column where i can track a number of days a column is checked. in my sheet, there is a check box field for Active status and another column named Active days. when the active status is checked, then it should record the number of days it is checked in the active days column. when someone unchecks it, the…","categoryID":322,"dateInserted":"2023-01-13T15:52:45+00:00","dateUpdated":"2023-01-13T15:54:51+00:00","dateLastComment":"2023-01-13T21:49:23+00:00","insertUserID":120420,"insertUser":{"userID":120420,"name":"asish lamichhane","url":"https:\/\/community.smartsheet.com\/profile\/asish%20lamichhane","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:26:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":120420,"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-01-13T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":32,"score":null,"hot":3347274728,"url":"https:\/\/community.smartsheet.com\/discussion\/99675\/tracking-the-cumulative-days-of-a-cell-value-change","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99675\/tracking-the-cumulative-days-of-a-cell-value-change","format":"Rich","lastPost":{"discussionID":99675,"commentID":357169,"name":"Re: Tracking the cumulative days of a cell value change","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357169#Comment_357169","dateInserted":"2023-01-13T21:49:23+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-01-13T22:02:45+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-01-13T20:26:13+00:00","dateAnswered":"2023-01-13T19:17:57+00:00","acceptedAnswers":[{"commentID":357132,"body":"

Sorry about that. I misunderstood the first time I read.<\/p>


<\/p>

You will need to set up the date checked column, and you will also need to set up a second sheet as a recipient for a copy row automation that copies when the box is unchecked. This will capture the static data on the second sheet for how many days the box was checked before it was again unchecked. You will also need a unique identifier on each row (Auto-Number column).<\/p>

Then the formula would be<\/p>

=SUMIFS({Static Sheet Days Checked Column}, {Static Sheet Unique ID Column}, @cell = [Unique ID]@row) + IF([Checkbox Column]@row = 1, TODAY() - [Checked Date]@row, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-13 20:26:11","updateUser":{"userID":120420,"name":"asish lamichhane","url":"https:\/\/community.smartsheet.com\/profile\/asish%20lamichhane","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:26:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"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":99678,"type":"question","name":"Copy a Formula to Multiple Rows, but Keep One Column Ref in the Formula the Same?","excerpt":"Hey all! I understand that when you copy a formula with criteria in certain columns to adjacent columns, it changes the column references to keep the relationship between columns the same (ie, column 10 has a formula that references col 1 & col 3, and copying the formula from col 10 to col 11 would result in the col 11…","categoryID":322,"dateInserted":"2023-01-13T17:40:00+00:00","dateUpdated":"2023-01-13T17:41:04+00:00","dateLastComment":"2023-01-13T17:54:42+00:00","insertUserID":154576,"insertUser":{"userID":154576,"name":"AmandaMerrigan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/AmandaMerrigan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:53:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":154576,"lastUserID":154576,"lastUser":{"userID":154576,"name":"AmandaMerrigan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/AmandaMerrigan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:53:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":13,"score":null,"hot":3347264682,"url":"https:\/\/community.smartsheet.com\/discussion\/99678\/copy-a-formula-to-multiple-rows-but-keep-one-column-ref-in-the-formula-the-same","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99678\/copy-a-formula-to-multiple-rows-but-keep-one-column-ref-in-the-formula-the-same","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":99678,"commentID":357101,"name":"Re: Copy a Formula to Multiple Rows, but Keep One Column Ref in the Formula the Same?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357101#Comment_357101","dateInserted":"2023-01-13T17:54:42+00:00","insertUserID":154576,"insertUser":{"userID":154576,"name":"AmandaMerrigan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/AmandaMerrigan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:53:23+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-01-13T17:54:49+00:00","dateAnswered":"2023-01-13T17:54:42+00:00","acceptedAnswers":[{"commentID":357101,"body":"

Looks like I just needed to reference the help center! I looked here but missed this one. For anyone else looking, just add a \"$\" to keep the column reference an absolute value.<\/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","log":{"dateUpdated":"2023-01-13 17:54:49","updateUser":{"userID":154576,"name":"AmandaMerrigan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/AmandaMerrigan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:53:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}">