有没有办法将十进制时间转换为常规时间,例如5.5=5:30。

huangapple go评论71阅读模式
英文:

Is there a way to convert decimal time to a regular time e.g. 5.5=5:30

问题

我已经提取了大量的数据并将其转换成了透视格式。有人要求以时间值而不是小数值显示时间。数据以小数值的形式输入,并包含许多行小数值。是否有办法在不手动使用公式更改它们的情况下进行转换?任何帮助将不胜感激。

英文:

I've pulled a large amount of data and put it into a pivot format. It has been requested to display the times in a time value instead of decimal value. The data came in a decimal value and had many rows of decimal values.
Is there any way to convert this without having to manually go and change them with a formula.
Any help is greatly appreciated

答案1

得分: 1

假设你的十进制时间是 5.5,你想要将其转换为 5:30。这个数值在单元格 A1 中。

  1. 在单元格 B1 中,输入 =MROUND(A1/24, 1/(24*60))。这个公式将把小数小时四舍五入到最近的分钟。
  2. 按下回车键。
  3. 现在,让我们格式化 B1 以显示时间。右键单击单元格 B1,然后选择格式单元格
  4. 类别列表中,选择时间
  5. 类型列表中,选择13:30
  6. 按下确定

现在,单元格 B1 应该显示 05:30,这是将 5.5 十进制小时转换为时间的结果。如果你有其他要转换的单元格,只需复制 B1 中的公式,然后粘贴到相应的单元格中。

英文:

Assume your decimal time is 5.5, which you want to convert to 5:30. This value is in cell A1.

  1. In cell B1, type =MROUND(A1/24, 1/(24*60)). This formula will round
    the decimal hours to the nearest minute.
  2. Press Enter.
  3. Now, let's format B1 to show time. Right-click on cell B1 and select
    Format Cells.
  4. From the Category list, choose Time.
  5. From the Type list, choose 13:30.
  6. Press OK.

Now, cell B1 should show 05:30, which is the conversion of 5.5 decimal hours to time. If you have other cells to convert, just copy the formula in B1 and paste it into the appropriate cells.

huangapple
  • 本文由 发表于 2023年7月27日 20:47:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/76779888.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定