Excel解析(xls)日期错误 [英] Excel parsing (xls) date error
问题描述
我正在一个项目中，我必须解析excel文件，以便客户端提取数据.这里弹出一个奇怪的事情:当我在Excel工作表中以5/9(5月9日)的格式解析日期时，我的程序中得到39577.我不确定这里是否编码了年份(这些工作表是2008年).
I'm working on a project where I have to parse excel files for a client to extract data. An odd thing is popping up here: when I parse a date in the format of 5/9 (may 9th) in the excel sheet, I get 39577 in my program. I'm not sure if the year is encoded here (it is 2008 for these sheets).
这些日期是距某种时代以来的天数吗?
Are these dates the number of days since some sort of epoch?
有人知道如何将这些数字转换为有意义的东西吗?我不是在寻找一种可以在解析时从excel文件中正确转换这些文件的解决方案(我们已经有成千上万个提取文件，需要人工选择相关信息-不能重新进行提取). /p>
Does anyone know how to convert these numbers to something meaningful? I'm not looking for a solution that would convert these properly at time of parsing from the excel file (we already have thousands of extracted files that required a human to select relevant information - re-doing the extraction is not an option).
推荐答案
Excel将日期存储为自0-JAN-1900以来的天数(因此1-JAN-1900的值为"1").您可以在此处找到有关Excel如何处理日期和时间的非常好的细分:
Excel stores dates as the number of days since 0-JAN-1900 (so 1-JAN-1900 would have a value of "1"). You can find a really good breakdown of how Excel handles dates and times here:
当日期在Excel中以"5/9"，"5月9日"或类似的日期出现在屏幕上时，这是格式上的窍门，并不代表基础数据值.听起来您的解析程序正在提取基础值，而不是格式化日期.但是，为了提出修复建议，我需要知道您的解析程序是什么(Excel宏，公式，外部代码等).
When dates appear on screen in Excel as "5/9", "May 9th", or some such, it is a trick of the formatting and is not representative of the underlying data value. It sounds like your parsing program is pulling the underlying value, not the formatted date. In order to suggest a fix, though, I need to know what your parsing program is (Excel macro, formula, outside code, etc.).
这篇关于Excel解析(xls)日期错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
