关于时间函数的问题，咨询了身边好多同事，都无解~~~

f359350181

miaojohn45 发表于 2024-3-29 22:59
=TEXT(SUM(IFERROR(--SUBSTITUTE(TEXTSPLIT(A1," "),"上午",-0.5),0)),"e/mm/dd hh:mm:ss AM/PM")

f359350181

啸海 发表于 2024-3-29 23:03
=IF((REPLACE(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,11),"上午 12","上午 0"),1,3,)+COUNT(FIND("下午",A1))*0 ...

老师，计算结果是错的呀。

quqiyuan

PQ

edwin11891

梳理了一下，导致紊乱的原因大致如下：

1. 上午12:00-12:59，其实是凌晨0:00-0:59；
2. 上午的其余时间不变，如上午5:00，就是早上5:00；
3. 下午12:00-12:59，就是24小时制的12:00-12:59；
3. 下午的其余时间，需要加上12小时，即0.5。
按照上述原则，公式如下，为便于理解，定义了名称和函数：

1. =LET(s,LAMBDA(src,LET(sDay,DATEVALUE(TEXTBEFORE(src," ")),sTime,--TEXTAFTER(src,"午 "),tA,IF(ISNUMBER(FIND("上",src)),IF(AND(sTime>12/24,sTime<13/24),-0.5,0),IF(AND(sTime>12/24,sTime<13/24),0,0.5)),eTime,sTime+tA,sWork,IF(OR(eTime>5/6,eTime<1/3),"夜班","白班"),TEXT(sDay,"yyyy/mm/dd")&" "&TEXT(eTime,"hh:mm")&" "&sWork)),s(A1))

复制代码

edwin11891

另外一个办法，就是直接把“上午”替换为AM，“下午”替换为PM，并放在末尾，即为正确时间。

按照此思路，公式为：

1. =LET(APM,IF(ISNUMBER(FIND("上",A1)),"AM","PM"),SX,IF(ISNUMBER(FIND("上",A1)),"上午","下午"),s,SUBSTITUTE(A1,SX,"")&" "&APM,sDay,TEXT(s,"yyyy-mm-dd hh:mm"),eTime,sDay-INT(sDay),sWork,IF(OR(eTime>5/6,eTime<1/3),"夜班","白班"),sDay&" "&sWork)

复制代码

象山海鲜

1. =-LOOKUP(,-(SUBSTITUTE(A7,{" 上午"," 下午"},)&{" 上午"," 下午"}))

复制代码

edwin11891

象山海鲜 发表于 2024-3-30 17:17

版主的方法简洁明快！乾坤大挪移方法学习了！

Excel地板砖

1. =TEXT(SUBSTITUTE(SUBSTITUTE(A1,"上午",),"下午",),"yyyy/mm/dd hh:mm:ss AM/PM")

复制代码

edwin11891

Excel地板砖 发表于 2024-3-30 18:47

有问题，下午3:15，应该是15:15，不是直接去掉“上午”、“下午”。

edwin11891

象山海鲜 发表于 2024-3-30 17:17

不使用技巧，直观思路下的公式（不用下拉）：

1. =LET(t,TEXTSPLIT(CONCAT(A1:A6&"@")," ","@"),d,CHOOSECOLS(t,1),w,CHOOSECOLS(t,2),s,CHOOSECOLS(t,3),--DROP((d&" "&s&" "&w),-1))

复制代码

加上白夜班判断：

1. =LET(t,TEXTSPLIT(CONCAT(A1:A6&"@")," ","@"),d,CHOOSECOLS(t,1),w,CHOOSECOLS(t,2),s,CHOOSECOLS(t,3),sDay,--DROP((d&" "&s&" "&w),-1),eTime,sDay-INT(sDay),sWork,IF(eTime>1/3,IF(eTime<5/6,"白班","夜班"),"夜班"),HSTACK(sDay,sWork))

复制代码