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>
评论
嗨，米歇尔，
就在这里。这取决于您的解决方案的设置方式。您可以共享解决方案或上传一些屏幕截图吗？
我希望这可以帮助你！
最好的，
AndréeStarå-工作流顾问 @完成
Smartsheet合作伙伴和顾问 /专家
安德烈·史塔罗|工作流顾问 /首席执行官 @大胆工作
W：www.workbold.com|E：[电子邮件保护]|P：+46（0）-72-510 99 35
请随时与我联系有关智能表，集成，一般工作流建议或其他其他内容的帮助。
请记住...在SmartSheet中跟踪时间只能工作几天。如果没有相当令人费解的解决方法，SmartSheet将无法处理实际时间（小时/分钟）。
thinkspi.com
@Paul Newcome是否仍然如此，我需要一个复杂的解决方法才能获得发电机运行多少时间？
试图计算发电机运行多少小时，并在单元格中没有数据时显示为空。在阅读了很多帖子之后，出现了一个公式，但L无法弄清楚如何删除“ #invalid值”错误，试图将公式包装在“如果错误”中但不起作用。
这是我目前的公式，但是必须有一些简单的东西，并且可以使单元显示为空白，如果两个饲料单元格为空白：
= value（左（[生成器＃2停止] @Row，（find（“：”，[生成器＃2启动]@row）@Row） - 1））） - value（左（[[Generator＃2 Time停止）@@行，（find（“：”，[生成器＃2 time停止] @Row） - 1）））） +（value（right（[生成器＃2 time启动]@row，（find（“：”，[Generator＃2停止] @Row） - 1））） - 值（右（[生成器＃2 time停止] @Row，（find（“：”，[Generator＃2 Time停止] @Row） - 1））） /60）
表格的屏幕截图：
谢谢！
@silvia rangel那是对的。SmartSheet尚未在时间计算中构建。我将在这篇文章底部包含一个链接，以在其中具有许多基于时间的解决方案的线程。
至于iFerror，它应该像这样缠绕整个公式。
=iferror（值（左（[生成器＃2停止] @Row，（find（“：”，[生成器＃2启动]@row）@Row） - 1））） - value（[[[Generator＃2 time停止）@ROW，（find（“：”，[生成器＃2时间停止] @Row） - 1）））） +（value（右（[生成器＃2 time启动]时间停止] @Row） - 1））） - 值（右（[生成器＃2 time停止] @row，（find（“：”，[生成器＃2 time停止] @row） - 1））） / 60），“”）
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":"2022-05-03T19:26:05+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时间
thinkspi.com
@Paul Newcome谢谢！因此，l意识到我在配方和昏迷的末端之间有一个空间
（60），“”）这给了我这个错误...另外，L在您的基于IME的解决方案帖子中尝试了公式，但没有一个为L我想要做的事情，至少在这张纸上，因为它们为他人工作！☺@silvia rangel很高兴您知道了。您还需要整体公式吗？我想，运行时间的负面结果并不准确。
thinkspi.com