如何格式化Google表格持续时间格式的单元格,以易读的格式显示天,小时和分钟? [英] How do I format a Google Sheets duration formatted cell to show days, hours and minutes in a human readable format?

查看:97
本文介绍了如何格式化Google表格持续时间格式的单元格,以易读的格式显示天,小时和分钟?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在Google表格中的单元格当前显示两个日期时间之间的时间差,该单元格的格式设置为持续时间,并显示为以小时,分钟和秒为单位的冒号分隔值.

The cell I have in Google Sheets currently shows the difference in time between two date-times, the cell is formatted as a duration and displays as a colon separated value in hours, minutes and seconds.

下面的例子.

788:42:07

如何格式化单元格以使其更易于阅读,特别是显示持续时间的天,小时和分钟的格式,并正确标记每个数字值?

How do I format the cell to be more human readable, specifically in the format that shows the days, hours and minutes of the duration with each numeric value labelled appropriately?

推荐答案

我发现有效的解决方案是编写一个公式,将现有的持续时间值转换为天,小时和分钟的各个持续时间分量值,然后将它们串联起来带有适当标记值的字符串的值.

The solution I found to be effective was writing a formula the converted the existing duration value into the individual duration component values of days, hours and minutes and then concatenates these values with strings that label the values appropriately.

公式如下:

= int(B4)&天& hour(mod(B4,1))&"小时&分钟(mod(B4,1))& quot;分钟"

其中B4是持续时间格式的包含值的单元格

with B4 being the duration formatted cell containing the value

788:42:07

包含解决方案公式的单元格现在显示持续时间单元格信息,如下所示:

The cell containing the solution formula now displays the duration cell information as follows:

32天20小时42分钟

我发现以下资源有助于发现该问题的解决方案.

I found the following resources useful in discovering the solution to this question.

https://infoinspired.com/google-docs/spreadsheet/convert-time-duration-to-day-hour-minute-in-google-sheets/

Google表格INT公式文档

Google表格MOD公式文档

Google表格的小时公式文档

Google表格TEXT公式文档

这篇关于如何格式化Google表格持续时间格式的单元格,以易读的格式显示天,小时和分钟?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆