格式化Excel中的日期(YY:MM:DD:时间) [英] Formatting Date(YY:MM:DD:Time) in Excel
问题描述
我有一个excel文件,有一个日期列,但我想将日期列转换为
YY / MM / DD / strong>
我已经搜索了2小时,但没有结果。
这是我的数据: p>
源数据: http://i.stack.imgur.com/75zbS.jpg
预期输出: YY / MM / DD /时间
有人能帮我怎么办吗?我想把它插入到postgresql,我想改变一切的兼容日期格式。
编辑:我试过右键单击 - >格式化单元格 - >日期,但它不更改任何内容!
感谢
并将日期和时间拆分成单独的单元格:
= DATE((LEFT(A1,4)), ,5,2)),MID(A1,7,2))
= TIME(MID(A1,10,2),MID(A1,12,2)),0)
一旦您的日期值采用Excel可以识别的格式,您就可以将格式更改为d like。
或者,如果你不关心你的值是一个可识别的日期格式,你可以得到你想要的格式,像这样(会给你一个字符串看起来像这样:YY / MM / DD / Time):
= MID(A1,3,2)& /& MID(A1,5,2)&/& MID(A1,7,2)&/& MID(A1,10,4)
I have an excel file, with a date column, but I want to convert the date column to
YY/MM/DD/Time
Ive been searching for 2 hours and no result yet.
This is my data:
Source Data: http://i.stack.imgur.com/75zbS.jpg
Expected Output: YY/MM/DD/Time
Can someone help me how I can do it? I want to insert it into postgresql and I want to change everything to compatible date format.
EDIT: I have tried Right Click -> Format cells -> date but it does not change anything!
Thanks
You could use this method and split the date and time into separate cells:
=DATE((LEFT(A1,4)),(MID(A1,5,2)),MID(A1,7,2))
=TIME(MID(A1,10,2),(MID(A1,12,2)),0)
Once your date value is in a format Excel can recognize, you can then change the formatting to whatever you'd like.
Or if you don't care to have the value in a recognizable date format, you can just get your desired formatting like this (will give you a string that looks like this: YY/MM/DD/Time):
=MID(A1,3,2)&"/"&MID(A1,5,2)&"/"&MID(A1,7,2)&"/"&MID(A1,10,4)
这篇关于格式化Excel中的日期(YY:MM:DD:时间)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!