当前位置:& >
> 使用公式提取Excel中的日期后发现格式不对
使用公式提取Excel中的日期后发现格式不对
如果你要提取Excel单元格中的日期，很可能会遇到日期格式不对，甚至是无论如何设置日期格式，结果还是不对。下面给你举个例子并给出解决的方案。假设A列，保存的是部分员工的身份证号码，当然，在A列中，也就保存了员工的出身日期。假如A1中的身份证号码是：086015首先说明，A列的数据格式为常规，就是常规，不允许改变格式。之后，当你试图将A1中的身份证号码中的出生日期截取后放在B1时，那么，我们其实可以使用函数来截取，一般，使用MID函数最为妥当了。公式自然就是如下的公式：=Mid(A1,7,8)然而，我们得到的结果却是：或许，你会马上意识到，是B1单元格中的数据格式不对，自然，格式有问题，当你把B列的数据格式都设置为日期格式中的&&时，你会惊奇的发现，结果还是一样。到此，本人还提醒阁下一句，你可以再设置C列为&&的日期格式，然后把B1中的数据复制了以后，使用选择性粘贴的办法，粘贴到C列，那么我告诉你，结果还是一样，仍然是那么，是什么导致这种问题的产生呢，又如何来解决这种问题呢?第一，是单元格的数据类型的问题，首先将其设置为&&的日期格式，是没有问题的，但是这一步，您必须做。第二，问题出在公式有问题，结果上述的公式从理论上来讲，是没有问题的。但是，问题却出在截取出生日期的时候，其截取方式有问题，使用如下的公式，问题就可以得到全面解决：=DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2))上述所得到的出生日期数据，其格式为，并且，未考虑单元格的前后是否有空格。如果单元格的前后有空格的话，上述公式就难以得到正确的结果了，而是使用如下的公式较为妥当。=DATE(MID(TRIM(A1),7,4),MID(TRIM(A1),11,2),MID(TRIM(A1),13,2))poi操作officePOI操作excel中的数据格式（日期类型） - 推酷
poi操作officePOI操作excel中的数据格式（日期类型）
7.3.3 POI中Excel文件Cell的类型
在读取每一个Cell的值的时候，通过getCellType方法获得当前Cell的类型，在Excel中Cell有6种类型，如表7-3所示。
表7-3 Cell的类型
CELL_TYPE_BLANK
CELL_TYPE_BOOLEAN
CELL_TYPE_ERROR
CELL_TYPE_FORMULA
CELL_TYPE_STRING
CELL_TYPE_NUMERIC
本例采用了CELL_TYPE_STRING和CELL_TYPE_NUMERIC类型，因为在Excel文件中只有字符串和数字。如果Cell的Type为CELL_TYPE_NUMERIC时，还需要进一步判断该Cell的数据格式，因为它有可能是Date类型，在Excel中的Date类型也是以Double类型的数字存储的。Excel中的Date表示当前时间与日相隔的天数，所以需要调用HSSFDateUtil的isCellDateFormatted方法，判断该Cell的数据格式是否是Excel Date类型。如果是，则调用getDateCellValue方法，返回一个Java类型的Date。
实际上Excel的数据格式有很多，还支持用户自定义的类型，在Excel中，选择一个单元格然后右键选择“设置单元格格式”，在弹出的单元格格式中选中“数字”，如图7-14所示。
图7-14 Excel的单元格格式
图中的数据有数值、货币、时间、日期、文本等格式。这些数据格式在POI中的HSSFDataFormat类里都有相应的定义。
HSSFDataFormat是HSSF子项目里面定义的一个类。类HSSFDataFormat允许用户新建数据格式类型。HSSFDataFormat类包含静态方法static java.lang.String getBuiltinFormat(short index)，它可以根据编号返回内置数据类型。另外static short getBuiltinFormat(java.lang.String format)方法则可以根据数据类型返回其编号，static java.util.List getBuiltinFormats()可以返回整个内置的数据格式列表。
在HSSFDataFormat里一共定义了49种内置的数据格式，如表7-4所示。
表7-4 HSSFDataFormat的数据格式
内置数据类型
&#,##0.00&
&($#,##0_);($#,##0)&
&($#,##0_);[Red]($#,##0)&
&($#,##0.00);($#,##0.00)&
&($#,##0.00_);[Red]($#,##0.00)&
&0.00E+00&
&d-mmm-yy&
&h:mm AM/PM&
&h:mm:ss AM/PM&
&m/d/yy h:mm&
保留为过国际化用
0x17 - 0x24
&(#,##0_);(#,##0)&
&(#,##0_);[Red](#,##0)&
&(#,##0.00_);(#,##0.00)&
&(#,##0.00_);[Red](#,##0.00)&
&_($*#,##0_);_($*(#,##0);_($* /&-/&_);_(@_)&
&_(*#,##0.00_);_(*(#,##0.00);_(*/&-/&??_);_(@_)&
&_($*#,##0.00_);_($*(#,##0.00);_($*/&-/&??_);_(@_)&
&_($*#,##0.00_);_($*(#,##0.00);_($*/&-/&??_);_(@_)&
&[h]:mm:ss&
&##0.0E+0&
&@& - This is text format
在上面表中，字符串类型所对应的是数据格式为&@&（最后一行），也就是HSSFDataFormat中定义的值为0x31（49）的那行。Date类型的值的范围是0xe-0x11，本例子中的Date格式为&&m/d/yy&&，在HSSFDataFormat定义的值为0xe（14）。
需要注意的一点是，所创建的Excel必须是在Microsoft Excel 97到Excel XP的版本上的，如果在Excel 2003中创建文件后，在使用POI进行解析时，可能会出现问题。它会把Date类型当作自定义类型。POI目前只提供对Microsoft Excel XP以下的版本的支持，在以后的版本中，希望会提供对Microsoft Excel 2003更好的支持。
已发表评论数()
请填写推刊名
描述不能大于100个字符!
权限设置： 公开
仅自己可见
正文不准确
标题不准确
排版有问题
没有分页内容
图片无法显示
视频无法显示
与原文不一致[切换城市]
[07-16] 怎么在EXCEL中更改日期格式存档
UID：77208
在线时间103小时
小鱼币438经验值101魅力值0人气值0
我想把EXCEL表格中的日期为02/06/2007改为格式的请问怎么做啊？我用右键——设置单元格式——日期中去改可是一点变化也没有，不知道为什么？因为数据量很大不可能一条一条手工更改 ，麻烦知道的YY帮忙一下哈，谢谢了
UID：80072
在线时间293小时
小鱼币657经验值2257魅力值0人气值47
应该可以呀,要不要我帮你改试试?
UID：31108
在线时间205小时
小鱼币1383经验值430魅力值0人气值0
你那个日期的写法是错误的。。。在excel里面是找不到你那种格式的。。。你可以把2007移动到开头位置()，在来转换日期格式就行。。。
UID：81494
在线时间68小时
小鱼币168经验值83魅力值0人气值0
不用改。只要你设置本来就是日期格式的话。你输，软件默认情况下还是。
UID：79905
在线时间531小时
小鱼币319经验值9466魅力值0人气值0
兵熊熊一个&&将熊熊一窝
UID：66944
在线时间91小时
小鱼币179经验值196魅力值0人气值0
LZ 可以尝试选中所有日期的单元格，通常在同一行或者同一列。然后右键点击单元格，设置属性。不要选“日期” 而是选“自定义”，其中有一个 yyyy-m-d 的格式，你参照这个输入一个 yyyy-mm-dd 就可以实现
的格式了。如果要实现 07-17-2007 或者 17-07-2007 应该也是同理，就是利用 mm-dd-yyyy，dd-mm-yyyy 啦。y (year) 代表年份的数字, yy就是两位数年份，yyyy 就是四位数。m (month) 代表月份的数字, m 就是一位数的，当然 10-12 还是会显示两位，mm 就是强制两位了， 01-12d (day) 代表日期, 道理同上。[ 本帖最后由 owenluo 于
12:15 编辑 ]
UID：77208
在线时间103小时
小鱼币438经验值101魅力值0人气值0
谢谢各位热心的YY了，可是不知道为什么，我收到的这份工作表我按照大家的方法做了，还是一点变化都没有 ，郁闷。。。。。。。。。
UID：66944
在线时间91小时
小鱼币179经验值196魅力值0人气值0
回复 #7 咚咚番茄 的帖子
看看文件有没有设置条件格式，有这种格式的情况下，条件格式会覆盖你手动更改的格式。如果是这样，就直接删除条件格式试试。
UID：77208
在线时间103小时
小鱼币438经验值101魅力值0人气值0
看了，没有设条件格式。叫同事帮我试试，结果在她的电脑上用设置单元格式——日期就改过来了。为什么我的电脑不行啊？是不是电脑系统设置的问题？以后总不能每次有这样的工作表时候都叫别人帮我改吧
UID：66944
在线时间91小时
小鱼币179经验值196魅力值0人气值0
回复 #9 咚咚番茄 的帖子
重装 Office 试试看？【这是万不得已才用的一招，也是不负责任的说法，呵呵】还有一些可能：你的日期是以文本方式存储在单元格里的。你在自己的电脑上新建一个 Excel 文件，按照在你同事电脑上操作的方式做同样的操作，如果能够将 01/06/2007 这样的格式转换成
的格式就说明你的系统和 Office 应该没有问题，关键在与你得到的 Excel 文件的问题。如果是从一些 MIS 或者 ERP 系统导出的数据，有可能会将所有的字段都作为文字内容而不是数字导出，这样使用日期的格式也没有办法更改其显示的表现。如果是这样的话，你就要把存储日期的数据转化成数字存储的方式先，我现在要开会去了，晚些再研究给你建议。[ 本帖最后由 owenluo 于
14:01 编辑 ]
UID：77208
在线时间103小时
小鱼币438经验值101魅力值0人气值0
Owen, 真的很感谢你的热心帮助 ，我试过了在新的EXCEL文件中01/06/2007我无法重新设置日期格式，如果我输入就可以将日期格式进行转换。应该是我系统的问题吧。对方的数据也是从他们系统导出直接生成的，跟他们协商下看能不能提供年月日的格式啦。 谢谢你
UID：66944
在线时间91小时
小鱼币179经验值196魅力值0人气值0
回复 #11 咚咚番茄 的帖子
是我犯了小学是常犯的错误，审题不清。果然是你记录中的格式不对。如果对方实在不方便更换格式，你可能就需要用一个宏来替换了。不是你系统的问题，是 Excel 默认就不认识 01/06/2007 的格式。三楼的 YUYU 一开始就说了，我也没注意。呵呵。后来自己打开 Excel 才发现果然如此。上网查了一下，感觉如果对方不方便改输出格式，你又不用宏的话下面的方法比较好，只是每次都要手动处理一下。在日期这个数据行下方或者数据列右侧插入行或者列。假设第一个日期单元格是 A1，在新插入的第一个单元格里输入 =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))，然后填充到整行，可以用鼠标拖。只要你的日期数据格式是 dd/mm/yyyy 就可以转换成日期格式的数据，再配合上面说的单元格格式就好了。如果你希望只保留处理好的数据，你还要新增一行或一列，把刚才公式算出来的数据按照之拷贝数值和格式的方法拷贝到新行、列，然后删除其他两行、列就好了。这是不得以的办法，最好还是能让对方输出比较好处理的数据格式。[ 本帖最后由 owenluo 于
17:45 编辑 ]
UID：77208
在线时间103小时
小鱼币438经验值101魅力值0人气值0
引用 原帖由 owenluo 于
17:02 发表 三楼的 Y ...
太感谢了.我用了这个公式,把问题解决了.好开心啊!!!!
宏俺不会,以后就用OUWEN教的这个了.帮我好大的忙啊,好人ING
P.S.感谢所有热心的YUYU
UID：77208
在线时间103小时
小鱼币438经验值101魅力值0人气值0
引用 原帖由 owenluo 于
17:02 发表 在日期这个数据行下方或者数据列右侧插入行或者列。假设第一个日期单元格是 A1，在新插入的第一个单元格里输入 =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))，然后填充到整行，可以用鼠标拖。只要你的日期数据格式是 dd/mm/yyyy 就可以转换成日期格式的数据，再配合上面说的单元格格式就好了。 太感谢了.我用了这个公式,把问题解决了.好开心啊!!!!
宏俺不会,以后就用OWEN教的这个了.帮我好大的忙啊,好人ING
P.S.感谢所有热心的YUYU
UID：66944
在线时间91小时
小鱼币179经验值196魅力值0人气值0
呵呵，我也是现学现用。Excel 是我最常用的办公软件了，大家共同进步。
UID：77208
在线时间103小时
小鱼币438经验值101魅力值0人气值0
恩, 现在也是天天和EXCEL打交道,发现功能真是强大.更发现自己很菜,呵呵......
访问内容超出本站范围，不能确定是否安全
还没有账号？
扫一扫下载客户端
关注小鱼微信帐号
版权所有，并保留所有权利。二次元同好交流新大陆
扫码下载App
温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
阅读(13671)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_',
blogTitle:'如何在EXCEl中把数字变成日期格式',
blogAbstract:'简单的设置单元格格式肯定解决不了问题的。 你这样，例如这个在A1单元格，你在B1输入如下公式： =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) 然后把B1设成日期型就好了， 如果必须在A1显示这个日，那你就选择B1－&复制－&选择A1-&按右键－&选择性粘贴－&选数值－&然后确定，A1设定成日期格式就好了， 注意：必须保证你输入的数字为8为，也就是月份和日期都是两位，日，你要写成。 解释： EXCEL的日期是从开始计算的，其实日期就是一个从开始的一个天数，比如你在一个单元格输入365，然后设定为日期型，所出现的日期就是，如果是364，那设定成日期型后就是. ',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:0,
publishTime:0,
permalink:'blog/static/',
commentCount:1,
mainCommentCount:1,
recommendCount:8,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}