返回特定日期的相同Vlookup。

克莱夫·华莱士
克莱夫·华莱士 ✭
在公式和函数

基本上我想做的是一个项目跟踪者。当他们做一个项目时，他们会花一定的时间在上面。这一切都很好，直到项目暂停并重新启动，我有一个问题，它计算出最接近的日期。所以我试着做的是，如果项目开始和结束在同一天，它会使用那个日期。

附件是我的原稿照片。你会看到它的填充物

image.png

看看小时是怎么变成负小时的。(项目开始时间/日期是从另一张表格中提取的)。在上面的照片中，它使用了简单的公式=VLOOKUP([项目编号]。[3，{系统表1范围2}，4，假)

因此，为了纠正这个问题，我使用了这个公式

=IF(AND(LEFT([Project Finish Time txt/Date] 3,2) = LEFT({System Sheet 1 Range 4}， 2)， VLOOKUP([Project No. 2])3， {System Sheet 1 Range 2}， 1, false) = [Project No.]3)， VLOOKUP([Project No.][3，{系统表1范围2}，4，假)) what i am trying to do in this formula is if the project started on the same date it ended uses that date to calculate the hours. Not the new date generated when the boys press start project again the next day. And the project no.s match

我得到了#无效数据类型

什么好主意吗?

为我糟糕的语法道歉

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

最好的答案

  • 克莱夫·华莱士
    克莱夫·华莱士 ✭
    ✓回答

    凯莉,

    我是澳大利亚人，所以日期不一样，我们今天读的是2020年4月5日。这就是为什么我试图使用LEFT(REF,2)，因为前两个数字是我们在一个月中的一天。

    对于公式

    =MAX(COLLECT({System Sheet 1 Range 4}， {System Sheet 1 Range 5}，[项目编号:@row，{系统表1范围4}，<[项目完成时间txt/日期]@row)) + ""

    得到0。

    =MAX(COLLECT({System Sheet 1 Range 4}， {4/4/20 System Sheet 1 Range 5}，[项目编号:@row，{系统表1范围4}，<[项目完成时间txt/日期]@row)) + ""

    我得到#无效ref

    所以澄清一下，我想从某个匹配的项目中获得时间。这是在同一天开始和结束的。

    例如，你正在建造一艘小船。这个项目可能会持续三周，所以每天你都要输入项目编号，然后输入到表格上。您不希望它在一天结束后计算出小时数，因此您再次输入项目编号并按“结束”。这将自动计算该项目当天的工作时间。但是，当您第二天再次开始相同的项目时，它会自动使用创建的时间来计算昨天的开始时间，而不是前一天早些时候的开始时间。很难解释。

    · 在Facebook上分享 在Twitter上分享

答案

  • 卡里。
    卡里。 员工
    编辑04/04/20

    克莱夫。你好!


    如果我想对了，您应该返回系统列中的“最大”日期，该日期小于特定项目行中系统列中的日期。首先可以使用COLLECT将具有相同项目编号和项目开始日期在项目完成日期之前的所有行聚集在一起。然后，您可以使用MAX来查找“最高”或最近的值。在我的试卷上，下面的公式是有效的:

    =MAX(COLLECT(当天查找项目开始时间/日期)}，{4/4/20当天查找源项目编号。}，[项目编号:@row，{当天查找项目开始时间/日期}，<[项目完成时间/日期]@row)) + ""

    {同一天查找项目开始时间/日期}是源工作表上具有开始时间/日期的系统列。

    {当日查找源项目编号。}为项目编号。列。

    + ""用于将结果转换为文本。


    在本例中使用COLLECT的好处是，您可以在希望通过指定人员筛选的事件中添加额外的范围和列。

    · 在Facebook上分享 在Twitter上分享
  • 克莱夫·华莱士
    克莱夫·华莱士 ✭
    ✓回答

    凯莉,

    我是澳大利亚人，所以日期不一样，我们今天读的是2020年4月5日。这就是为什么我试图使用LEFT(REF,2)，因为前两个数字是我们在一个月中的一天。

    对于公式

    =MAX(COLLECT({System Sheet 1 Range 4}， {System Sheet 1 Range 5}，[项目编号:@row，{系统表1范围4}，<[项目完成时间txt/日期]@row)) + ""

    得到0。

    =MAX(COLLECT({System Sheet 1 Range 4}， {4/4/20 System Sheet 1 Range 5}，[项目编号:@row，{系统表1范围4}，<[项目完成时间txt/日期]@row)) + ""

    我得到#无效ref

    所以澄清一下，我想从某个匹配的项目中获得时间。这是在同一天开始和结束的。

    例如，你正在建造一艘小船。这个项目可能会持续三周，所以每天你都要输入项目编号，然后输入到表格上。您不希望它在一天结束后计算出小时数，因此您再次输入项目编号并按“结束”。这将自动计算该项目当天的工作时间。但是，当您第二天再次开始相同的项目时，它会自动使用创建的时间来计算昨天的开始时间，而不是前一天早些时候的开始时间。很难解释。

    · 在Facebook上分享 在Twitter上分享
  • 克莱夫·华莱士
    克莱夫·华莱士 ✭

    太棒了，起作用了，谢谢

    · 在Facebook上分享 在Twitter上分享
  • 卡里。
    卡里。 员工

    太好了!很乐意帮忙!

    · 在Facebook上分享 在Twitter上分享

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
Hey @Marilen.Navarro103391<\/a> <\/p>

Please use the formula I provided. It was intentional for the character \"~\" to be included. <\/p>

=MID(Name@row, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)) + 1, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 6)) - 1 - FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)))<\/p>

Kelly<\/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":106552,"type":"question","name":"Problem w\/Index - Match formula","excerpt":"I using the following formula, =INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}), 0) And it works for the first row when i input in the contract number into the column. On subsequent rows when i input the contract number it pulls back incorrect information and if I put in a fictious number it pulls…","categoryID":322,"dateInserted":"2023-06-16T15:13:32+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:36:24+00:00","insertUserID":162467,"insertUser":{"userID":162467,"name":"Ed Smartsheet","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Ed%20Smartsheet","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJyYOjKt3k3tneIFbUPuoJzZbnd9quLS-Wdt5b7l=s96-c","dateLastActive":"2023-06-16T19:45:18+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-16T17:50:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3373859396,"url":"https:\/\/community.smartsheet.com\/discussion\/106552\/problem-w-index-match-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106552\/problem-w-index-match-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106552,"commentID":381031,"name":"Re: Problem w\/Index - Match formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381031#Comment_381031","dateInserted":"2023-06-16T15:36:24+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-16T17:50:53+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-16T19:45:15+00:00","dateAnswered":"2023-06-16T15:36:24+00:00","acceptedAnswers":[{"commentID":381031,"body":"

Hey @Ed Smartsheet<\/a> <\/p>

In addition to the IFERROR, it looks like you have the , 0 in the INDEX portion of the formula instead of the MATCH. Try moving it in one:<\/p>

=INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}, 0))<\/strong><\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106550,"type":"question","name":"Invalid Operation","excerpt":"Good morning, I am trying to sum the entries that are greater than 30 but less than 60 days old. =SUMIFS({011-AP Archive -# of Days to Complete}, >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":34,"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":[]}],"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":[]}">

公式和函数趋势

  • 分割文本

    有人能帮帮我吗?我需要一个关于如何分割文本的公式。例LOW - VS-87 - Service-FS电池更换铅-MEGA MALL-1259410我需要一个只写MEGA MALL的专栏。

  • 问题w/索引匹配公式

    使用以下公式，=INDEX({合同焦点}，MATCH([合同编号])。@row，{合同号})，0)当我在列中输入合同号时，它对第一行起作用。在随后的行中，当我输入合同号时，它会拉回不正确的信息，如果我输入一个虚构的数字，它会拉……

  • 无效的操作

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