Excel将小时转换为天数的公式

分享于 

2分钟阅读

电脑

  繁體
问题:

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天。

这些值是文本格式,不是日期或时间。


答案1:

可以使用此函数获取(如果在A2单元格)时间单元格中的数字:


=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))



把它放到E5中,然后你可以将它转换为天和小时:


= QUOTIENT(E5,24) & IF(E5/24<2," Day"," Days") & MOD(E5,24) & IF(MOD(E5,24)<2," Hour"," Hours")




= 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"))




答案2:

=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","")





for  form  DAYS  HOUR  
相关文章