Excel日期格式 [英] Excel Date Formatting
问题描述
我有一个大的导入csv文件包含美国日期列。我想使用代码将这些日期更改为英国格式(dd / mm / yyyy)
。问题是美国的日期都在mm / dd / yyyy
和m / dd / yyyy
格式 - 后者不会对excel中的任何可点击选项(包括文本到列)过程做出响应。有没有一个代码,在相邻列中运行时会进行转换?
您还可以使用 TEXT()
函数很容易(注意:源数据必须是excel日期值)
TEXT(value,format_text)
其中值
是参考单元格, format_text
是如何格式化文本 - 在您的情况下,dd / mm / yyyy。
假设:
A1 = 3/17/2013
A2 = 12/27/2013
在B1& B2简单地输入:
B1 = TEXT(A1,dd / mm / yyyy)
B2 = TEXT A2,dd / mm / yyyy)
,结果应为
AB
3/17/2013 17/03/2013
12/27/2013 27/12/2013
希望有所帮助。
UPDATED SUGGESTION如果使用文本:
使用 mid()
, left()
和 right()
函数然后检查月份mm是1或2个字符长使用 LEN()
函数。最后使用& amp;和/运算符。
尝试将其粘贴到B1中,应该可以正常工作:
= MID(A1,FIND(/,A1,1)+1,2)&/& IF(LEN(LEFT(A1,FIND(/,A1)-1))= 1 (A1,FIND(/,A1)-1),LEFT(A1,FIND(/,A1)-1))& code>
I have a large imported csv file containing American Dates in a column. I want to change these dates to UK format (dd/mm/yyyy)
using a code. The problem is the US dates are in both "mm/dd/yyyy"
and "m/dd/yyyy"
format- the latter of which does not respond to any of the "clickable" options in excel- including the "Text to Columns" procedure. Is there a code which when run in the adjacent column will do the conversion?
You can also use the TEXT()
function quite easily (Note: source data must be an excel date value)
TEXT(value, format_text)
where value
is the reference cell and format_text
is how you want to format the text- in your case dd/mm/yyyy.
Assuming:
A1 = 3/17/2013
A2 = 12/27/2013
In B1 & B2 simply input:
B1 = TEXT(A1, "dd/mm/yyyy")
B2 = TEXT(A2, "dd/mm/yyyy")
and the result should be
A B
3/17/2013 17/03/2013
12/27/2013 27/12/2013
Hope that helps.
UPDATED SUGGESTION IF WORKING WITH TEXT:
Split the string using mid()
, left()
and right()
functions then check to see if the month mm is 1 or 2 characters long using the LEN()
function. Finally concatenatr the string together using the & and / operators.
Try pasting this in B1, it should work fine:
=MID(A1,FIND("/",A1,1)+1,2)&"/"&IF(LEN(LEFT(A1,FIND("/",A1)-1))=1,0&LEFT(A1,FIND("/",A1)-1),LEFT(A1,FIND("/",A1)-1))&"/"&RIGHT(A1,4)
这篇关于Excel日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!