时间位于一个单元格中时的时间差

时间位于一个单元格中时的时间差

这看起来可能重复,但我的问题与过去其他人的问题不同。我甚至尝试了 9 年前类似问题中发布的解决方案,但仍然不起作用。

我正在整理老板制作的时间表,以便贴在墙上,我无法将开始和结束时间分开到各自的单元格中。我知道那样会更简单,但这不是她想要的,所以我必须保留她的设计。

我希望屏幕截图中时间上方的小数字显示时差。最右边的总时间已经计算了这些小数字。我只想给她新的 Excel 文档,她只需更改小时数,让数学为她完成所有其他工作。

因此,当在单个单元格中输入“7:00-3:30”时,其上方的单元格将计算时间并显示“8”,我希望这样可以起作用,因此如果此人的开始和结束时间都是下午时间,那么它仍然会起作用。

我知道这算错了,如果白天总工作时间差超过 6 小时工作时间,我就会取消每个人享有的 30 分钟午休时间。

截屏

文件 -->https://easyupload.io/m3xbos

以下是我正在使用的示例,只是为了让您了解它是什么样子,但我上面提供的文件并不是实际的时间表。它只是一个没有杂乱内容的测试示例文件。我甚至添加了我与 ChatGPT 的对话,这对我解决问题有所帮助。

答案1

此公式假设时间始终采用 x:xx 格式,绝不会是 7:30-11 之类的格式。这个公式很大而且有点混乱,但它本质上是相同的基本构建块,重复几次以适应不同的场景。

    =IF(A1="OFF",0,(IF(IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)>0.25,
IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)-(0.5/24),
IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)))*24)

我将尝试将其分解为几个更简单的部分。

首先检查时间是否为“OFF”,如果为 0,则输入 0,否则进行难看的数学运算:

=IF(A1="OFF",0,{long and ugly, but simple stuff})

以下计算连字符后的时间:

RIGHT(A1,LEN(A1)-FIND("-",A1))*1

以下计算连字符之前的时间:

LEFT(A1,FIND("-",A1)-1)*1

然后将它们组合起来,但要检查如果右侧时间分量小于左侧时间分量,则执行“右 + 0.5 - 左”(12 小时是 0.5 天),否则执行“右 - 左”:

IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)

然后,它周围还有另一个 IF 包装器,表示如果小时数 > 0.25(6 小时是一天的 0.25),则执行 (RL 或 R+0.5-L),但再减少半小时(0.5/24),否则将其保留为 (RL 或 R+0.5-L)。此后,将结果乘以 24,即可从一天的一小部分转换为小时的整数:

IF(IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)>0.25,
IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)-(0.5/24),
IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1))*24

答案2

LET()如果您拥有 Excel 365 并且有可用函数,则可以使用以下公式:

=LET(cell, A1, sep, FIND("-",cell), from, TRIM(LEFT(cell,sep-1)), to, TRIM(RIGHT(cell,LEN(cell)-sep)), gross, MOD((TIMEVALUE(to)- TIMEVALUE(from))*24,12), gross - IF(gross>6,0.5,0))

只需更改单元格引用(“A1”)

不使用 LET() 也可以做同样的事情,但是由于多次重复,该案例公式会更长

在此处输入图片描述

相关内容