标记日期和时间重叠

保罗新来的

@Megha布看起来在COUNTIFS中的第一个范围之后有一个额外的右括号。

=如果(条件统计(网站:网站 )..........

把它拿掉，看看它是否适合你。

穆尼·帕万·库马尔·雷迪·科纳

@Paul新来的，

我读过这个博客。它非常有用。

我不希望时间重叠。对我来说，只有特定列的日期重叠才是重要的。

我对这些公式感到困惑。我只有2列“任务开始日期”，“任务结束日期”。我想在“任务结束日期”栏中触发重叠，当2个更多的日期相同时。你能帮帮我吗?

保罗新来的

@Muni Pavan Kumar Reddy Kona您是只想比较结束日期列，还是也想合并开始日期列?

穆尼·帕万·库马尔·雷迪·科纳

@Paul新来的，我只想在所有单元格中比较结束日期，如果两个或多个结束日期相等，我们需要标记

保罗新来的

@Muni Pavan Kumar Reddy Kona如果您只是想标记具有相同结束日期的任何行，它看起来像这样…

=IF(COUNFITS([End Date]:[End Date]， [End Date]@row) > 1, 1)

穆尼·帕万·库马尔·雷迪·科纳

@Paul新来的，

它不工作，请查收附件的屏幕截图。

Error.JPG

保罗新来的

@Muni Pavan Kumar Reddy Kona你说它不起作用到底是什么意思?它没有标记重复的日期还是你得到了一个错误代码?

穆尼·帕万·库马尔·雷迪·科纳

@Paul新来的对不起。它工作吗?你给出的实际公式如下。

=IF(COUNFITS([End Date]:[End Date]， [End Date]@row) > 1,1

但是小错字=IF(COUNTIFS([End Date]:[End Date]， [End Date]@row) > 1,1)

我把它们作为标志，这对我来说很好。谢谢你的帮助。

保罗新来的

@Muni Pavan Kumar Reddy Kona很抱歉。有时候我的手指移动得太快了。

TsmithFastSigns

@Paul新来的我看到你为别人做的一张纸上面写着日期和时间重叠的标志。你能再说一遍吗?

保罗新来的

@TsmithFastSigns看看下面的帖子。里面应该有你需要的东西。我想现在已经有9页了。哈哈哈

I have decided to create a post that consolidates all of the different time based solutions that I have put together.<\/p>

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.<\/p>

I thought it would be helpful to have them all in one place instead of spread out.<\/p>

I welcome all feedback, suggestions, and additions.<\/p>

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.<\/p>

I will also try to post various tips and tricks as I think of them as well such as:<\/p>

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:<\/p>

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))<\/p>

Calculating Time Worked for Employees<\/a><\/p>

Can you calculate time in Smartsheet?<\/a><\/p>

Conversion of timezones (Solved Formula Included)<\/a><\/p>

Date\/Time\/Year<\/a><\/p>

Flagging a Date and Time Overlap<\/a><\/p>

How do I create time of day columns?<\/a><\/p>

Need to create a "shift" column from a time column<\/a><\/p>","bodyRaw":"[{\"insert\":\"I have decided to create a post that consolidates all of the different time based solutions that I have put together.\\nSome of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.\\n\\nI thought it would be helpful to have them all in one place instead of spread out.\\n\\nI welcome all feedback, suggestions, and additions.\\n\\nI'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.\\n\\nI will also try to post various tips and tricks as I think of them as well such as:\\nIt is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:\\n=VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) + IF(CONTAINS(\\\"p\\\", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) = 12, -12))\\n\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662\"},\"insert\":\"Calculating Time Worked for Employees\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d861f6ccf0e048b98906495ed74671fc\"},\"insert\":\"Can you calculate time in Smartsheet?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=0122690b7ff744c68653e4dd0fa3cd81\"},\"insert\":\"Conversion of timezones (Solved Formula Included)\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=8b6ddddb66bb4b3db6fbbec745b46795\"},\"insert\":\"Date\\\/Time\\\/Year\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d39961b785de4312ba2c98b8d148b3bd\"},\"insert\":\"Flagging a Date and Time Overlap\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767\"},\"insert\":\"How do I create time of day columns?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=54f270e0ed1440f6a25de2453119655c\"},\"insert\":\"Need to create a \\\"shift\\\" column from a time column\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-06-08T15:55:08+00:00","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-15T21:50:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68947\/formulas-for-calculating-time","embedType":"quote","name":"Formulas for Calculating Time"}"> https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

TsmithFastSigns

@Paul新来的你是这个社区的宝贵财富。好男人!

TsmithFastSigns

我做错了什么?@Paul新来的

=IF(COUNTIFS([VEHICLE #]:[VEHICLE #]， [VEHICLE #]@row, DATE:DATE，(电子邮件保护)，完成:完成，@cell >=(电子邮件保护)， START:开始，@cell <=(电子邮件保护)> 1,1)

我把你的[虚拟驾照]改成了[车牌号]。我正在试着制定一个车辆时间表，通知车辆使用的重叠。

我得到“不正确的参数设置”作为一个错误。

保罗新来的

@TsmithFastSigns您只是忘记关闭COUNTIFS。

=IF(COUNTIFS([VEHICLE #]:[VEHICLE #]， [VEHICLE #]@row, DATE:DATE，(电子邮件保护)，完成:完成，@cell >=(电子邮件保护)， START:开始，@cell <=(电子邮件保护)) > 1,1)