Excel VBA 阻止 VBA 将时间值转换为小数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15373504/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA prevent VBA from converting Time values to decimals
提问by Mallow
I am trying to extract data from my workbook because I made the workbook wrong but I want to keep the data. So I have to go through each worksheet and extract the specific bits of data I need. (Repetitive task = vba code to the rescue)
我试图从我的工作簿中提取数据，因为我把工作簿弄错了，但我想保留数据。所以我必须浏览每个工作表并提取我需要的特定数据位。（重复任务 = vba 代码来救援）
I'm done, but I have one nasty little problem.
我已经完成了，但我有一个讨厌的小问题。
I am using ws.Range("C13") to extract each cell that I need. It's a cell that has time values like 10:00 or 22:13. When I debug.print that value however I get a decimal. Why? How do I prevent it from converting my "10:30" to "0.4375"
我正在使用 ws.Range("C13") 来提取我需要的每个单元格。这是一个具有 10:00 或 22:13 等时间值的单元格。但是，当我 debug.print 该值时，我得到了一个小数。为什么？如何防止它将我的“10:30”转换为“0.4375”
From what I can tell, the decimal isn't even related to the time value. WRONG it is simply the time value as a representation of a 24 hour period. So it is related. But still.
据我所知，小数点甚至与时间值无关。错误的是，它只是作为 24 小时周期表示的时间值。所以是有关系的。但是还是。
回答by barrowc
ws.Range("C13")is the equivalent of ws.Range("C13").Valuewhich returns the underlying data from that cell.
ws.Range("C13")相当于ws.Range("C13").Value从该单元格返回基础数据。
If instead you use ws.Range("C13").Text, you will get the displayed text from the cell. If you have set a specific date/time format for that cell then calling ws.Range("C13").Textshould return text in that specific format
如果改为使用ws.Range("C13").Text，您将从单元格中获得显示的文本。如果您为该单元格设置了特定的日期/时间格式，则调用ws.Range("C13").Text应以该特定格式返回文本
回答by Ken White
The time values are correct; it's the way Excel stores date and time values internally.
时间值是正确的；这是 Excel 在内部存储日期和时间值的方式。
Dates are stored as floating point values, where the whole portion represents the number of days since 12/30/1899 00:00.00(date 0.0) and the decimal part represents the fractional part of a single day (eg., 408875.5represents 11/28/2011 12:00:00 PM).
日期存储为浮点值，其中整个部分表示自12/30/1899 00:00.00(date 0.0)以来的天数，小数部分表示一天的小数部分（例如，408875.5表示11/28/2011 12:00:00 PM）。
Here are some conversion factors you might find useful:
以下是一些您可能会觉得有用的转换因素：
Decimal value Time Value Calculation
============= ========== ===========
0.00094444444 12:01:00 AM 1.0 / 24 / 60 (1 day/24 hours/60 minutes per hour)
0.01041666666 12:15:00 AM 1.0 / 24 / 60 * 15
0.02083333333 12:30:00 AM 1.0 / 24 / 60 * 30
0.04166666666 01:00:00 AM 1.0 / 24
回答by RayanFar
hours = MOD(INT(0.tttttttt*24), 24)
minutes = MOD(INT(0.tttttttt*24*60), 60)
seconds = MOD(INT(0.tttttttt*24*60*60), 60)
milliseconds = MOD(INT(0.tttttttt*24*60*60*1000), 1000)
