将包含小时，分钟，秒的字符串转换为十进制小时 [英] Convert a string containing hours, minutes, seconds to decimal hours

问题描述

我有一个自动生成的Excel表格，其中包含一个包含表示时间跨度的字符串值的列。这些值具有以下格式：

  17小时，6分16秒
  

我需要将它们转换为显示小时数（例如17.1小时）。我怎样才能做到这一点？

解决方案

您不必使用VBA。

  = 24 * VALUE（SUBSTITUTE（SUBSTITUTE（B4，hours $
分钟，：），秒，））
  

这将删除秒字符串，并用：字符替换小时和分钟分隔符字符串。 VALUE 然后将其解释为日期/时间，其计算为一天的几分之一;所以你的17小时6分16秒例如0.712685185天。将此乘以24，则可显示十进制小时数，即17.1044。

为了使这更加强大，您可以从 SUBSTITUTE 开始删除空格，但上面给出了大概的概念。

如果您必须在VBA中执行此操作，那么我将这样做：

 code> Dim myTimeString As String 
 Dim splitTime（）As String 
 Dim decimalHours As Double 
 
 myTimeString =17小时6分16秒
 
'删除无用字符
 myTimeString =修剪（替换（替换（替换（myTimeString，，），_ 
，，），秒 ））
'由一个有用的分隔符替换hours和minutes，：
 myTimeString = Replace（Replace（myTimeString，minutes，：），hours ：）
'String现在看起来像这样：17：6：16。现在拆分它：
 splitTime = Split（myTimeString，：）
 
 decimalHours = CInt（splitTime（0））+ CInt（splitTime（1））/ 60 + _ 
 CInt（splitTime（2））/ 3600 
 
 
'或者，将字符串转换为日期，然后转换为十进制小时
 Dim myDate As Date 
 myDate = CDate（myTimeString）
 decimalHours2 = 24 * CDbl（myDate）'相同的结果。 
  

I have an automatically generated Excel sheet that has a column containing string values representing time spans. The values have the following format:

17 hours, 6 minutes, 16 seconds

I need to convert them to show decimal hours (e.g 17.1 hours). How can I do that?

解决方案

You don't have to use VBA. This worksheet formula will do the trick.

=24*VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4," hours, ",":"),
     " minutes, ",":")," seconds",""))

This deletes the "seconds" string and replaces the "hours" and "minutes" delimiter strings with the : character. VALUE then interprets this as a date/time, which evaluates to a fraction of a day; so for your "17 hours, 6 minutes, 16 seconds" example, 0.712685185 days. Multiplying this by 24 gives decimal hours, i.e. 17.1044.

To make this more robust, you could start by SUBSTITUTEing out the spaces, but the above gives you the general idea.

If you must do it in VBA, then I would do it like this:

Dim myTimeString As String
Dim splitTime() As String
Dim decimalHours As Double

myTimeString = "17 hours, 6 minutes, 16 seconds"

' Remove useless characters
myTimeString = Trim(Replace(Replace(Replace(myTimeString, " ", ""), _
    ",", ""), "seconds", ""))
' Replace "hours" and "minutes" by a useful delimiter, ":"
myTimeString = Replace(Replace(myTimeString, "minutes", ":"), "hours", ":")
' String now looks like this: "17:6:16". Now split it:
splitTime = Split(myTimeString, ":")

decimalHours = CInt(splitTime(0)) + CInt(splitTime(1)) / 60 + _
    CInt(splitTime(2)) / 3600


' Alternatively, convert the string to a date, then to decimal hours
Dim myDate As Date
myDate = CDate(myTimeString)
decimalHours2 = 24 * CDbl(myDate) ' same result.

这篇关于将包含小时，分钟，秒的字符串转换为十进制小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！