name day1 day2 day3 day4 day5 totalhr DAY
person1 2hr 4hr 1.5hr 1hr 2hr 11.5
Person2 3hr 2.4hr 2hr 2hr 3hr 12.5
Person3 3hr 2hr 2hr 2hr 3hr 12hr
我有一张包含文本值的 Excel 表(如上面的示例)。
我想要一种方法来将它们转换为天数,例如:
如果我有 12 小时,它会四舍五入到最接近的天数,变成 1 天。如果超过 12 小时,它可以说例如 1 天 2 小时。直到 2 小时增加到 12 小时,它才变成 1 天。
值得注意的是一般格式而不是日期或时间。
我发现时间让事情变得如此困难。
我们可以从输入值中忽略小时数吗:它应该是总共 12 = 1 天 3 小时(其中 3 是小时数或不含小时数)。
我不想转换为字符串。如果公式是没有小时就很好。我的观点是如何得到 2、3、1,每个代表小时,当总共 12 时,就变成了一天。12 小时 = 1 天。您可以在公式中忽略小时。
谢谢
答案1
怎么样:
=IF(MOD(SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr",""),24)>=12,ROUND(SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr","")/24, 0), FLOOR(SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr","")/24,1)&" D "&MOD(SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr",""),24)&" H")
E5
例如,您的手机在哪里?
解释:
SUBSTITUTE(SUBSTITUTE(E5,"hrs",""),"hr","")
删除“hrs”和“hr”。
然后另一部分检查MOD
小时数是否> = 12:
- 如果是,则四舍五入到第二天
- 如果否,则写下
FLOOR
,然后写下小时数
答案2
您可以使用此函数从包含时间的单元格中获取数字(如果时间在单元格 A2 中):
=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))
您可以通过在函数中使用此公式来添加多个单元格的值SUM()
,每个单元格一个值。
假设你把它输入进去E5
然后你可以用以下方法将其转换为天数和小时数:
= QUOTIENT(E5,24) & IF(E5/24<2, " Day ", " Days ") & MOD(E5,24) & IF(MOD(E5,24)<2, " Hour", " Hours")
并将少于一天的任何内容放入如下的 if 语句中:
= IF(E5/24<1, "1 Day",QUOTIENT(E5,24) & IF(E5/24<2, " Day ", " Days ") & REST(E5,24) & IF(REST(E5,24)<2, " Hour", " Hours"))
资料来源: https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/