这看起来可能重复,但我的问题与过去其他人的问题不同。我甚至尝试了 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