计算工时的公式

瓦利德

大家好!

请参阅附上的照片上面

我正在寻找一种方法来计算我的员工每天的工作时间。

Col名称(Shift Start和Shift End)在24小时内按下菜单

(小时和分钟用冒号分隔)

我需要一个公式列(工作/小时)，计算员工的工作时间的数量，并考虑24小时格式的情况下，在两个不同的日子的转变。

例如:

员工在18:00(晚上)开始轮班，03:00(早上)结束轮班。我们需要计算工作小时数和分钟数的公式，即(8小时50分钟)

如果相反，计算相同的时间(晚上开始和第二天早上结束)

谢谢你的帮助

希望有一天SmartSheet能够开发出一种列式的日期/时间功能和带有自动化功能的时间功能，我已经等了很多年，希望在SmartSheet上看到这个功能

瓦利德

我将不胜感激，如果让我知道，如果没有公式为这个要求，不要担心它或工作

她名叫Stara

嗨@Waleed

我希望你一切安好!

看看保罗关于不同时间解决方案的广泛帖子;其中至少有一个是你需要的。

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

那有用吗?

我希望这对你有帮助!

注意安全，周末愉快!

最好的

她名叫Stara|工作流程顾问/ CEO @工作大胆

✅我的帖子是否帮助或回答了你的问题或解决了你的问题?请支持社区将其标记为“有见地”/“投票赞成”、“很棒”或“并且”作为公认的答案．这将使其他人更容易找到解决方案或帮助回答!

瓦利德

嗨@Andree Stara

我想邀请@Paul新来的

我尝试了用户发布的样本公式，它不适合我

我仍然需要在这个精确给出样品的公式上的帮助，我是新手

吉纳维芙P。

嗨@Waleed

保罗的第一个链接应该有一个与你需要的类似的解决方案，这个:计算员工工作时间

第一列公式更新为您的列名:

=((VALUE(LEFT([Shift End]@row, FIND(":"， [Shift End]@row) - 1)) + VALUE(RIGHT([Shift End]@row, 2)) / 60) + ([Shift End]@row - [Shift Start]@row) * 24) - (VALUE(LEFT([Shift Start]@row, FIND(":"， [Shift Start]@row) - 1) + VALUE(RIGHT([Shift Start]@row, 2)) / 60))

瓦利德

嗨@Genevieve P。

谢谢你的帮助。

我在列总时间上尝试了相同的代码来计算工作小时数/分钟

不幸的是，它没有为我工作，错误消息:#无效的操作

吉纳维芙P。

嗨@Waleed

对不起，在翻译你的列名时，我用错误的名字替换了两个单元格。

其中两个参考文献是日期列的开始日期和结束日期-以Paul发布的工作表为例。

我已经持有两个引用，你需要改变与日期列相关联:

=((值(左(转变结束@row,发现(“:”,[转变结束]@row) - 1)) +价值(右(转变结束@row, 2)) / 60) + ((结束日期)@row-(开始日期)@row) * 24) - (VALUE(LEFT([Shift Start]@row, FIND(":"， [Shift Start]@row) - 1)) + VALUE(RIGHT([Shift Start]@row, 2)) / 60)

欢呼,

吉纳维芙

瓦利德

嗨@Genevieve P。

太棒了，效果很完美，我得到了我想要的结果

你的帮助让我们度过了美好的一天，非常感谢，吉纳维芙女士

我唯一的问题是，我放置公式的列显示错误消息#无效值，如果一些目标单元格是空白的，我必须全部填充它们，错误被替换为结果。

有没有办法不出现这个错误信息?这是我用的最后一个公式

=((VALUE(LEFT([End Shift]@row, FIND(":"， [End Shift]@row) - 1)) + VALUE(RIGHT([End Shift]@row, 2)) / 60) +([结束日期]@row -[开始日期]@row) * 24) - (VALUE(LEFT([Start Shift]@row, FIND(":"， [Start Shift]@row) - 1) + VALUE(RIGHT([Start Shift]@row, 2)) / 60))

再次感谢大家

吉纳维芙P。

嗨@Waleed

我很高兴能帮上忙!

为了消除错误，你可以在前面添加一个IF语句，如果单元格是空白的，返回空白，否则执行公式:

=IF([End Shift]@row = ""， ""，公式)

或

=如果(@row最终转变 = "", "", (( 值(左(晚班@row,找到(”:“(晚班)@row) - 1)) +价值(右(晚班@row, 2)) / 60) +([结束日期]@row -[开始]@row) * 24) -(值(左(开始转变@row,发现(“:”,[开始转变]@row) - 1)) +价值(右(开始转变@row, 2)) / 60))

欢呼,

吉纳维芙

瓦利德

嗨@Genevieve P。

效果很好，

我觉得更强大的智能表有它定制为我们的大部分工作流程，非常感谢帮助