第一篇:各种日期格式的转换公式
excel常用函数公式大全:各种日期格式的转换公式
(2012-11-15 11:18:01)转载▼ 标签: 分类: 精品转载
杂谈
不少朋友提问关于excel中各种功能的实现方法、excel函数的编写以及应用方法,经过寻修网http://www.xiexiebang.com/多方收集,现在将常用的excel常用函数公式逐一整理出来,供广大网友们借鉴,如果还需要实现什么功能,也可在寻修网上提问,我们一定及时补充。各种日期格式的转换公式
将 “01/12/2005” 转换成“20050112”格式 =RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)=YEAR($A2)&TEXT(MONTH($A2),“00”)&TEXT(DAY($A2),“00”)该公式不用设置数据有效性,但要设置储存格格式。也可以用下列两方法:
1、先转换成文本, 然后再用字符处理函数。
2、[数据]-[分列] [日期]-[MDY] 将“2005年9月”转换成“200509”格式
先用公式:=text(a1,“yyyymm”)+0 然后将单元格格式为常规。
将“2005-8-6”格式转换为“20050806”格式
用公式:=TEXT(A1,“YYYYMMDD”)反之,将20050806转为日期2005-8-6格式,可用公式: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))另四种公式:
=text(a1,“0000-00-00”)显示:2005-08-06 =--TEXT(A1,“#-00-00”),把单元格设置为日期格式 显示:2005-8-6 =TEXT(20050806,“0000-00-00”)*1,单元格设置日期型 显示:2005-8-6 =VALUE(LEFT(A1,4)&“-”&MID(A1,5,2)&“-”&RIGHT(A1,2))显示:2005-8-6 将“20060501”转换为“2006-05-01”格式
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))将“199306”转换为“1993-6”
公式1:=LEFT(A3,4)&“-”&RIGHT(A3,2)*1 公式2:=--TEXT(A3*100+1,“#-00-00”)公式2需要设置单元格格式,自定义:e-m 公式3:=TEXT(TEXT(A3&“01”,“0000-00-00”),“e-m”)把198405转换成1984.05
一、查找—1984,替换—1984.二、如果全部是年月的话,寻修网http://www.xiexiebang.com/建议,1、采取辅助=mid(xxxxxx,1,4)& “.” & right(xxxxxx,2)
2、选中这列,用数据中的分列。然后„„„„„
三、单元格格式/数字/自定义,类型下面输入:####“.”## 将文本“2004.01.02” 转换为日期格式:2004-1-2 =DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))将2005-8-6转换为2005年8月6日格式
=TEXT(A1,“yyyy”“年”“m”“月”“d”“日”“;@”)象22怎样转换成22日?转成当年当月的日子 公式为:=date(year(now()),month(now()),22)将“2006年5月”转换成“2006年05月”
公式为:=TEXT(A8,“yyyy”“年”“mm”“月”“;@”)也可以这样处理:选中单元格,设置单元格公式-数字-自定义,将yyyy“年”m“月”改为:yyyy“年”mm“月”,即可。但这方法打印出来显示为:2006/5/ 将“1968年6月12日”转换为“1968/6/12”格式
=YEAR(A1)&“/”&MONTH(A1)&“/”&DAY(A1)显示:1968/6/12 =TEXT(A1,“yyyy/mm/dd”)显示:1968/06/12 将“1968年6月12日”转换为“1968-6-12”格式
=YEAR(A1)&“-”&MONTH(A1)&“-”&DAY(A1)显示:1968-6-12 =TEXT(A1,“yyyy-mm-dd”)显示:1968-06-12 将1993-12-28的日期格式转换成1993年12月
=CONCATENATE(YEAR(A1),“年”,MONTH(A1),“月”)=YEAR(A1)&“年”&MONTH(A1)&“月” 也可以自定义格式 [$-404]e“年”m“月” 将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式 =year(A1)&text(month(A1),“00”)要将“99.08.15” 格式转换成“1999.08.15”如何做
选中列,数据菜单中选分列,分列过程中“格式”选“日期YMD”,结束。
要保持2005/8/6格式 当输入2005/8/6后系统自动变成2005-8-6,要保持2005/8/6格式,可以使用强制文本(前面加'号)或使用公式=TEXT(A1,“YYYY/MM/DD”)。也可以用另一种公式:=IF(ISERROR(TEXT(A1,“yyyy/mm/dd”)),TEXT(A1,“0000!/00!/00”),TEXT(A1,“yyyy/mm/dd”))将“二○○三年十二月二十五日”转为“2003-12-25”格式,1、可以用数组公式将中文日期转化为日期系列数{=14610+MATCH(SUBSTITUTE(A3,“元”,“一”),TEXT(ROW($14611:$55153),“[DBNum1]yyyy年m月d日”),0)} 该公式速度较慢。
2、改进后的公式,速度要快的多:
{=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),“[DBNum1]0000”),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),“元”,“一”),TEXT(ROW($1:$366),“[DBNum1]m月d日”),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),“元”,“一”),TEXT(ROW($1:$366),“[DBNum1]m月d日”),0)))}
要设置为1900年的日期格式。
日期格式转换
如A列是月份数为8,B列是日期数为18,如何在C列显示“8月18日” =A1&“月”&B1&“日” 反之,要将C列的“8月18日” 直接分别到D、E列,显示月份和日期,月数份=LEFT(C5,FIND(“月”,C5)-1)日期数=MID(C5,FIND(“月”,C5)+1,FIND(“日”,C5)-FIND(“月”,C5)-1)也可分别用公式: =month(--c5)=day(--c5)日期格式转换问题 输入的日期是:04-07-26.与另一格的“001”合并,合并出来是:040726001.=TEXT(A1,“YYMMDD”)&“001” 要想自动取得“编制日期:XXXX年X月X日”
可在该单元格输入 =“编制日期:”&TEXT(TODAY(),“yyyy年m月d日”)excel中如何将文本格式数字转为数字格式
| 浏览:16656 | 更新:2013-03-01 10:48 | 标签:excel
2
3
4
5 6
7
分步阅读
在工作中,我们经常发现一些通过文本文件或其它财务软件的数据导入Excel中后居然是以文本形式存在的,即使是重新设置单元格格式为数字也无济于事。如下图几个数据,就是文本格式。我们通过单元格格式设置后还是文本格式。那么如何来转换成数字格式呢?
工具/原料
excel 2003 方法/步骤
1.1 我们可以在不进行操作的空白区域单元格内输入1,如下图,这个1是为了下面步骤中参与计算用的数据;选中数据1的单元格,这里要保证这个单元格的格式为数字格式;
2.2 选中这个单元格,执行菜单中的“编辑”-“复制”命令,我们当然也可直接按键盘上的ctrl+c键;
3.3 然后再选中所要转换格式的数据范围,如下图中的 A1至A3单元格数据;
4.4 选中数据后,我们点击菜单中的“编辑”-“选择性粘贴”;
5.5 打开“选择性粘贴”对话框,如下图所示;
6.6 在“选择性粘贴”对话框中,我们在“运算”栏中找到“乘”,在前的单选框前打勾;选择好后点击“确定”;
7.7 之后大家仔细观察,区域中右上角出现一个提示符号,点击下三角打开选项,我们找到“转换为数字”;
8.8 现在大家看下文本是不是变数字格式了。大家注意:数字默认是右对齐,而文本是左对齐的。
100806040200第一季度第三季度东部西部北部
第二篇:日期格式转换公式
日期格式转换公式
将 “01/12/2005” 转换成“20050112”格式 =RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)
=YEAR($A2)&TEXT(MONTH($A2),“00”)&TEXT(DAY($A2),“00”)该公式不用设置数据有效性,但要设置储存格格式。也可以用下列两方法:
1、先转换成文本, 然后再用字符处理函数。
2、[数据]-[分列]
[日期]-[MDY] 将“2005年9月”转换成“200509”格式 先用公式:=text(a1,“yyyymm”)+0 然后将单元格格式为常规。
将“2005-8-6”格式转换为“20050806”格式 用公式:=TEXT(A1,“YYYYMMDD”)
反之,将20050806转为日期2005-8-6格式,可用公式: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))另四种公式: =text(a1,“0000-00-00”)显示:2005-08-06 =--TEXT(A1,“#-00-00”),把单元格设置为日期格式 显示:2005-8-6 =TEXT(20050806,“0000-00-00”)*1,单元格设置日期型 显示:2005-8-6 =VALUE(LEFT(A1,4)&“-”&MID(A1,5,2)&“-”&RIGHT(A1,2))显示:2005-8-6 将“20060501”转换为“2006-05-01”格式 =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))将“199306”转换为“1993-6”
公式1:=LEFT(A3,4)&“-”&RIGHT(A3,2)*1
公式2:=--TEXT(A3*100+1,“#-00-00”)公式2需要设置单元格格式,自定义:e-m 公式3:=TEXT(TEXT(A3&“01”,“0000-00-00”),“e-m”)象22怎样转换成22日?转成当年当月的日子 公式为:=date(year(now()),month(now()),22)将“2006年5月”转换成“2006年05月”
公式为:=TEXT(A8,“yyyy”“年”“mm”“月”“;@”)
也可以这样处理:选中单元格,设置单元格公式-数字-自定义,将yyyy“年”m“月”改为:yyyy“年”mm“月”,即可。但这方法打印出来显示为:2006/5/ 将“1968年6月12日”转换为“1968/6/12”格式 =YEAR(A1)&“/”&MONTH(A1)&“/”&DAY(A1)显示:1968/6/12 =TEXT(A1,“yyyy/mm/dd”)显示:1968/06/12 将“1968年6月12日”转换为“1968-6-12”格式 =YEAR(A1)&“-”&MONTH(A1)&“-”&DAY(A1)显示:1968-6-12 =TEXT(A1,“yyyy-mm-dd”)显示:1968-06-12 将1993-12-28的日期格式转换成1993年12月 =CONCATENATE(YEAR(A1),“年”,MONTH(A1),“月”)=YEAR(A1)&“年”&MONTH(A1)&“月” 也可以自定义格式 [$-404]e“年”m“月”
将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式 =year(A1)&text(month(A1),“00”)
要将“99.08.15” 格式转换成“1999.08.15”如何做
选中列,数据菜单中选分列,分列过程中“格式”选“日期YMD”,结束。
要保持2005/8/6格式
当输入2005/8/6后系统自动变成2005-8-6,要保持2005/8/6格式,可以使用强制文本(前面加'号)或使用公式=TEXT(A1,“YYYY/MM/DD”)。也可以用另一种公式:=IF(ISERROR(TEXT(A1,“yyyy/mm/dd”)),TEXT(A1,“0000!/00!/00”),TEXT(A1,“yyyy/mm/dd”))
将“二〇〇三年十二月二十五日”转为“2003-12-25”格式,1、可以用数组公式将中文日期转化为日期系列数{=14610+MATCH(SUBSTITUTE(A3,“元”,“一”),TEXT(ROW($14611:$55153),“[DBNum1]yyyy年m月d日”),0)} 该公式速度较慢。
2、改进后的公式,速度要快的多:
{=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),“[DBNum1]0000”),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),“元”,“一”),TEXT(ROW($1:$366),“[DBNum1]m月d日”),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),“元”,“一”),TEXT(ROW($1:$366),“[DBNum1]m月d日”),0)))} 要设置为1900年的日期格式。日期格式转换
如A列是月份数为8,B列是日期数为18,如何在C列显示“8月18日”
=A1&“月”&B1&“日”
反之,要将C列的“8月18日” 直接分别到D、E列,显示月份和日期,月数份=LEFT(C5,FIND(“月”,C5)-1)
日期数=MID(C5,FIND(“月”,C5)+1,FIND(“日”,C5)-FIND(“月”,C5)-1)也可分别用公式: =month(--c5)=day(--c5)日期格式转换问题
输入的日期是:04-07-26.与另一格的“001”合并,合并出来是:040726001.=TEXT(A1,“YYMMDD”)&“001”
要想自动取得“编制日期:XXXX年X月X日”
可在该单元格输入 =“编制日期:”&TEXT(TODAY(),“yyyy年m月d日”)
第三篇:日期格式转换
2011-04-22 15:10
Excel中日期格式转换问题
Excel中日期格式转换问题
“urn:schemas-microsoft-com:office:office” />
1.数字格式转年月日格式
工作表中出生年月日格式格式是如:19870714 19870906 20090902如果想改成1987-7-14或改成1987年7月14日。
1、就用此函数插入一列如B列,B1=(LEFT(A1,4)&“-”&MID(A1,5,2)&“-”&RIGHT(A1,2));其它单元格可以用此公式自动填充,然后把此单元格设置成日期1987-7-14 ;
2、插入一列如B列,B1=(LEFT(A1,4)&“年”&MID(A1,5,2)&“月”&RIGHT(A1,2)&“日”);其它单元格可以用此公式自动填充,然后把此单元格设置成日期1987年7月14 日。
把这种格式2009.9.2转换成2009-09-02这样的格式:假如原始数据在A列从A1开始,则在B1输入公式=TEXT(SUBSTITUTE(A1,“.”,“-”),“yyyy-mm-dd”),然后下拉复制公式即可。
2.在Excel中将非日期格式数据转换为日期格式
如果Excel表格中有大量的类似“19841006”、“19841029”非日期格式的数据要转换为日期格式,如“1984-10-6”,可以用分列的方法:
1).选择需要转换单元格或区域,单击菜单“数据→分列”,弹出“文本分列向导-3之步骤1”对话框。如果是Excel 2007,则在功能区中选择“数据”选项卡,在“数据工具”组中,单击“分列”按钮。
2).单击两次“下一步”,在“文本分列向导-3之步骤3”中,在“列数据格式”中选择“日期”。还可以根据需要,在其后的下拉列表中选择一种日期格式。本例为默认的“YMD”格式。
3).单击“完成”按钮,Excel会在原单元格或区域中将数值格式的“19841006”和文本格式的“1984.10.6”转换为日期格式“1984-10-6”。
另外,对于“1984.10.6”文本格式的数据,还可以将“.”替换为“-”,也可以转换为日期格式。
3.将日期格式转换为数字格式
在日期后插入一列 取函数 =text(a1,“yyyymmddhhmm”)yyyy表示年mm表示月dd表示日hh表示时mm表示分 a1表示日期位置(第一行第一列),然后向下拖曳填充即可改变其他各行日期
4.Excel文本日期转换为日期格式
什么是序列号? Excel 可将日期存储为可用于计算的序列号。默认情况下,1900 年 1 月 1 日的序列号为 1,2008 年 1 月 1 日的序列号为 39,448,这是因为它距 1900 年 1 月 1 日有 39,448 天。
2、若要将转换公式复制到相邻单元格的区域中,请选择在其中键入公式的单元格,然后拖动填充柄
“urn:schemas-microsoft-com:vml” />,使之覆盖大小与包含文本日期的单元格区域匹配的空单元格区域。
拖动填充柄之后,应有序列号与包含文本日期的单元格区域相对应的单元格区域。
3、选择包含序列号的单元格或单元格区域,然后在“开始”选项卡上的“剪贴板”组中,单击“复制”。
键盘快捷方式 也可以按 Ctrl+C。
4、选择包含文本日期的单元格或单元格区域,然后在“开始”选项卡上的“剪贴板”组中,单击“粘贴”下面的箭头,然后单击“选择性粘贴”。
5、在“选择性粘贴”对话框的“粘贴”下,选择“值”,然后单击“确定”。
6、在“开始”选项卡上的“数字”组中,单击“数字格式”框旁边的箭头,然后单击“短日期”或“长日期”。
提示 对于其他日期格式,请单击“其他数字格式”。在“设置单元格格式”对话框中的“数字”选项卡上,单击“分类”框中的“日期”,然后在“类型”框中单击所需的日期格式。
7、若要在成功转换所有日期后删除序列号,请选择包含这些序列号的单元格,然后按 Delete。
5.数字9681002转化成1968-10-0
2像“19681002”这种,直接设置单元格格式,只能在显示上像日期,但实际数据不能像日期那样直接用函数或公式来计算。
最好是用公式进行转换,但结果只能放在另一单元格中。公式转换的方法很多,如:
=--TEXT(A1,“0000-00-00”)
再将单元格格式设为日期格式。(公式前面的“--”不能省。)
6、如何把Excel中日期格式19970124转换成1997-1-24?
执行,数据,分列,下一步,下一步,在选择框中选择“日期”后面格式为YMD 最后点击完成即可。
7、如何把Excel中日期格式19970124转换成1997-01-24?
1、假如日期在A1,可以在B1中输入:
=MID(A1,1,4)&“-”&MID(A1,5,2)&“-”&MID(A1,7,2)
2、复制B1,选中A1,鼠标右键,选择性粘贴,数值,确定
3、删除B1完成操作
(注意将A1设为常规或数值格式)
8、如何把EXCEL中的日期格式1997-1-24转换成数值型格式19970124?
1、假如日期在A1,可以在B1中输入:=TEXT(A1,“yyyymmdd”)
2、复制B1,选中A1,鼠标右键,选择性粘贴,数值,确定
3、删除B1完成操作
设为常规或数值格式)
9.出生年月日阿拉伯数字分别转换成中文小写
答案一;
A2=1990-1-28B2=NUMBERSTRING(TEXT(A2,“YY”),3)C2=NUMBERSTRING(MONTH(A2),1)D2=NUMBERSTRING(DAY(A2),1)
答案二
年 =TEXT(RIGHT(YEAR(A2),2),“[DBNum2]0”)月:
=TEXT(MONTH(A1),“[DBNum2]0”)
日可照葫芦画瓢
答案三.1990-1-28年
=TEXT(MID(YEAR(A1),3,1),“[DBNum2]”)&TEXT(MID(YEAR(A1),4,1),“[DBNum2]”)
月=TEXT(MONTH(A1),“[DBNum1]”)
日=TEXT(DAY(A1),“[DBNum1]”)
答案四
年=TEXT(TEXT(A1,“yy”),“[dbnum2]0”)
月=TEXT(MONTH(A1),“[dbnum1]d”)
日=TEXT(DAY(A1),“[dbnum1]d”)
如果11显示成一十一,那么请将红字部分去掉
答案五
=SUBSTITUTE(NUMBERSTRING(TEXT(A1,“yyyy”),3)&“年
”&TEXT(MONTH(A1),“[dbnum1]”)&“月”&TEXT(DAY(A1),“[dbnum1]”)&“日”,“一十”,“十”)
10.将年月日转换为8位数字
=text(A1,“yyyymmdd”)
第四篇:EXCEL中文日期与英文日期的转换
一、一般的转换方法
假如A列为英文日期格式,譬如是:
A1 19-Jul-12
A2 20-Jul-12
A3 21-Jul-12
A4 22-Jul-12
那么,如果你想将其转换为中文的日期,假如放在B列,首先你得将B列设置为中文的日期格式,方法是:选择B列,然后找到菜单格式;单元格。
在对话框中,找到日期,在右边的类型里面找到一种中文的日期格式,比如:二00一年三月十四日
然后,直接在B1中使用简单公式: =A1 这样,就转换为中文日期了,其它单元格直接使用填充就搞定了。
这样,在B列,我们将得到如下的中文日期
二○一二年七月十九日
二○一二年七月二十日
二○一二年七月二十一日
二○一二年七月二十二日
二○一二年七月二十三日
如上的方法,直接使用单元格的数据类型的方法设置就可以了。
二、其它的转换方法
①种情况
如果英文日期形式为:May 10rd,2012
像这种数据直接设置单元格格式中的日期格式是不管用的
要想变为中文日期形式,首先:把日期中的日(也就是中间的那个数)后面的rd、th、st、nd等替换为空
然后再设置单元格的格式中日期格式为yyyy-m-d(也可换成你想要的日期格式),即可
②种情况
假如有这样的英文日期数据为: Feb 18 2008 12:00AM
这样的格式 通过如下的函数可以转换成数字型的
假设你的日期在A1
=TEXT(MID(a1,5,2)&-&LEFT(a1,3)&-&MID(a1,8,4)& &MID(a1,12,6),YYYY日MM月DD日,上午/下午h时mm分)
第五篇:Excel表格日期格式转换
我想填写 1994年08月 但是却变成 1994年8月 在哪设置?
有两种方法:
一、设置单元格格式:选定需要输入日期的单元格——右键——选择“设置单元格格式”——“数字”选项卡(一般进去后就是)——在左侧选择框中选择“文本”——确定,这样你在该单元格中输入的日期就不会变了。
二、直接输入:选定需要输入日期的单元格——在需要输入的内容之前加一个半角状态下的单引号,你的提问中的输入内容应为
'1994年08月
选择单元格-右键-单元格格式-自定义 在类型框把格式代码改为 yyyy“年”mm“月”
比如说我A1设置时2011.01.21-2011.2.20,我想要的效果是我回车后在A2的时候自动显示2011.02.21-2011.3.20.。这个是高难度吧。有会的吗?谢谢了 在A2输入=TEXT(EDATE(1*SUBSTITUTE(LEFT(A1,10),“.”,“-”),1),“e.mm.dd”)&“-”&TEXT(EDATE(1*SUBSTITUTE(RIGHT(A1,10),“.”,“-”),1),“e.mm.dd”),回车,向下拖动公式即可
Excel表格日期格式转换大全
在Excel中我们常常会输入各种日期,但有时候为了节省时间我们 可能输入的不是那么正规,这时候就可以设置一下Excel中的日期格式自 动转换功能,即可轻松转换成自己想要的格式了。本篇教程会为大家详细 讲解Excel中常见的一些输入日期转换的方法!
第一种日期转换:输入“20110606”时,自动转换为:2011-06-06!
首先,选中单元格,然后右键选择“设置单元格格式”,单击左边的“分类”里面的“自定义”,然后在右 边的“类型”中输入“0000-00-00”确定就OK了。
第二种日期转换:输入“20110606”时,自动转换成“2011年06月06日”!
步骤同上,只是在“单元格格式”的“类型”中输入“####年##月##日”确定即可。
第三种日期转换:输入“20110606”,转换为“贰零壹零年零陆月零陆日”!
在“单元格格式”自定义“类型”中输入“[dbnum2]0000年00月00日”
第四种日期转换:将“20110606”转换为“二○一○年○六月○六日”!
在“类型”中输入“[dbnum1]0000年00月00日”,确定!