Excel VBA阻止VBA将时间值转换为小数 [英] Excel VBA prevent VBA from converting Time values to decimals
问题描述
我试图从工作簿中提取数据,因为我把工作簿弄错了,但是我想保留数据。因此,我必须遍历每个工作表并提取所需的特定数据位。 (重复任务=救援的vba代码)
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)
我已经做好了,但是我有一个讨厌的小问题。
I'm done, but I have one nasty little problem.
我正在使用ws.Range( C13)提取我需要的每个单元格。该单元格的时间值为10:00或22:13。当我debug.print该值但是我得到一个十进制。为什么?如何防止它将我的 10:30转换为 0.4375
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"
据我所知,小数甚至与时间值无关。
错误,它只是将时间值表示为24小时周期。因此它是相关的。
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.
推荐答案
时间值正确;这是Excel内部存储日期和时间值的方式。
The time values are correct; it's the way Excel stores date and time values internally.
日期存储为浮点值,其中整个部分代表自以来的天数。 12/30/1899 00:00.00
(日期 0.0
),小数部分表示一天的小数部分(例如, 408875.5
表示 11/28/2011 12:00:00 PM
)。
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.5
represents 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
这篇关于Excel VBA阻止VBA将时间值转换为小数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!