TODAY()函数正在替换之前的日期

Allison_S
Allison_S
编辑12/09/19 公式和函数

你好,

我在生产电子表格中有一列,用于输入生产成员ob欧宝娱乐app手机下载完成订单的日期。列的设置如下—一列是首字母列,生产成员在完成订单后选择他们的首字母。ob欧宝娱乐app手机下载第二列包含公式“=TODAY()”,它填充生产成员输入首字母(基于首字母列)的当前日期。ob欧宝娱乐app手机下载

我发现这个函数在整个星期都按预期运行,但是在星期一早上-当第一个人登录到生产表时,任何之前有星期五日期的内容都被替换为星期一日期。ob欧宝娱乐app手机下载示例—生产团队在10ob欧宝娱乐app手机下载月26日星期五完成订单,当第一个用户在10月29日星期一登录到工作表时,所有日期为10月26日的订单都被第一个登录到工作表的人替换为10月29日的订单。我能做些什么来阻止这种情况,或者有什么其他功能选项来收集日期并使其坚持下去?

任何关于如何防止这种情况的想法或建议都将非常感谢!!谢谢你!

标签:

评论

  • Brian W
    Brian W ✭✭

    是的,TODAY()函数将始终更新为今天的日期。我认为永久输入日期的唯一方法是手动输入。

  • 谢谢你的知识,谢谢你的建议。

    如果这是真的,为什么除了星期五之外,它在所有其他订单上都保留了以前的日期?周一至周四以订单完成日期为准。如果今天函数总是默认为当前日期-我的电子表格中的所有日期将始终是当前日期?事实不是这样吗?

    如有任何进一步的想法,我将不胜感激。:-)

    截屏2018-11-12下午1点39分33秒

  • 迈克Wilday
    迈克Wilday ✭✭✭✭✭✭

    您能否将完整的公式粘贴到本文中,以便我们帮助您分析并确定为什么会发生变化?

  • 当然,给你。

    =IF([Artist - Pints]1 <> "", TODAY())

  • 迈克Wilday
    迈克Wilday ✭✭✭✭✭✭

    有趣。无论何时打开工作表,该项目都应更新。因此,如果你只在周一打开表单,那么你可能只在打开当天看到了变化。你每周都打开它吗?根据你发布的公式,任何时候你打开工作表,它确定艺术家pints不是空白的,它应该更新该字段到当前日期。

  • 迈克Wilday
    迈克Wilday ✭✭✭✭✭✭

    你能确定是否有人在某个时候硬编码了这些日期吗?你有没有试过——右键点击没有改变的手机历史记录,看看是否有人手动设置了日期?”

  • 我确实点击了单元格历史记录,它显示了周一早上第一个登录到工作表的人更新日期,但不是所有的日期单元格(这很奇怪)。我们每天在生产中使用这张表,这就是为什么我对日期变化在各行中是随ob欧宝娱乐app手机下载机的感到困惑,并且只与星期五/星期一的实例一致。

  • 迈克Wilday
    迈克Wilday ✭✭✭✭✭✭

    其中一行没有改变…你可以点击进去看看它是否仍然是一个公式吗?还要检查“Artist Pints”是否为空白。如果在相同的行中是空的。

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

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

请查看公式手册模板!
I would suggest a helper column (can be hidden after setting up) that determines the weighted % on each parent row.<\/p>

=IF([Weight (% of Completion]@row <> \"//www.santa-greenland.com/community/discussion/35501/\", [Weight (% of Completion]@row * [% Complete]@row)<\/p>


<\/p>

Then to add each piece together, you would use<\/p>

=SUM(CHILDREN([Helper Column]@row))<\/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":106318,"type":"question","name":"Look up multiple column values from Sheet B to match values in Sheet A to return value from Sheet B","excerpt":"Hello, I'm trying to make a formula that counts values from Sheet A against multiple columns in Sheet B, if only 1 total match is found, then lookup values from Sheet A against Sheet B and based on the result, pull in the value from a Status column. If either too many same results or no results are found return a text…","categoryID":322,"dateInserted":"2023-06-12T14:48:55+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T11:45:16+00:00","insertUserID":158192,"insertUser":{"userID":158192,"name":"Clare123","url":"https:\/\/community.smartsheet.com\/profile\/Clare123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-13T12:26:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-13T16:08:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":55,"score":null,"hot":3373242851,"url":"https:\/\/community.smartsheet.com\/discussion\/106318\/look-up-multiple-column-values-from-sheet-b-to-match-values-in-sheet-a-to-return-value-from-sheet-b","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106318\/look-up-multiple-column-values-from-sheet-b-to-match-values-in-sheet-a-to-return-value-from-sheet-b","format":"Rich","lastPost":{"discussionID":106318,"commentID":380268,"name":"Re: Look up multiple column values from Sheet B to match values in Sheet A to return value from Sheet B","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380268#Comment_380268","dateInserted":"2023-06-13T11:45:16+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-13T16:08:13+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-13T10:56:36+00:00","dateAnswered":"2023-06-13T09:33:21+00:00","acceptedAnswers":[{"commentID":380250,"body":"

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

I hope you're well and safe!<\/p>

Yes, absolutely.<\/p>

Here's the structure.<\/p>

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))<\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":106314,"type":"question","name":"Flexible Duration value when calculating scheduling dates","excerpt":"I have a Smartsheet which is a project plan with the Dependencies and Duration columns is being used in scheduling calculations to automatically deriving the start\/end dates for each task. However, I need the Duration to be dynamic e.g. linking cell to a cell in another sheet or to contain a formulae.. However, I cannot…","categoryID":322,"dateInserted":"2023-06-12T12:39:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T11:22:45+00:00","insertUserID":162290,"insertUser":{"userID":162290,"name":"Mark Duddy","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Mark%20Duddy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-13T11:41:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-13T16:02:16+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":56,"score":null,"hot":3373230151,"url":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","format":"Rich","lastPost":{"discussionID":106314,"commentID":380264,"name":"Re: Flexible Duration value when calculating scheduling dates","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380264#Comment_380264","dateInserted":"2023-06-13T11:22:45+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-13T16:02:16+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-13T11:24:19+00:00","dateAnswered":"2023-06-13T11:22:45+00:00","acceptedAnswers":[{"commentID":380264,"body":"

Hi @Mark Duddy<\/a> <\/p>

If you're using dependencies in a Project Sheet, then the Duration column is not able to be updated via formulas <\/a>or cell links. This is because it's used in combination with the Start and Finish dates in your sheet to automatically generate data (e.g. if you add the Start date and a Duration, the Finish date populates. If you add the Start date and Finish date, the Duration automatically populates).<\/p>

You could set up your sheet to use regular columns and use your own formulas to generate automatic dates based on a text\/number \"duration\" column, but then these details would not be associated with Critical paths or other Project features in a Gantt chart.<\/p>

Cheers,<\/p>

Genevieve<\/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":9979,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势