Excel2003里这样的时间如何转换?9/28/2010 12:43:50 PM转换成09/29/2010 00:43:50
题目
Excel2003里这样的时间如何转换?9/28/2010 12:43:50 PM转换成09/29/2010 00:43:50
有如下两点注意:1,格式 9/5/2010 10:30:06 PM要求变为09/05/2010 22:30
2,9/28/2010 12:43:50 PM转换后应是09/29/2010 00:43:50
如下公式:
=LEFT(A1,FIND(" ",A1))&IF(RIGHT(A1,2)="PM",TEXT(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)+12/24,"hh:mm:ss"),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1))有缺陷,但我不会改,
9/28/2010 12:43:50 PM转换后是9/28/2010 00:43:50这是不对的
答案
=IF(LEN(LEFT(A1,FIND("/",A1)))=2,"0"&LEFT(A1,FIND("/",A1)),LEFT(A1,FIND("/",A1)))&IF(MID(A1,FIND("/",A1)+2,1)="/","0"&MID(A1,FIND("/",A1)+1,2),MID(A1,FIND("/",A1)+1,3))&MID(A1,FIND(" ",A1)-4,5)&IF(RIGHT(A1,2)="PM",TEXT(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)+12/24,"hh:mm:ss"),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1))
举一反三
我想写一篇关于奥巴马的演讲的文章,写哪一篇好呢?为什么好
最新试题
热门考点
- (x2-5x)(x2-5x-2)-24分解因式
- I like green tea and orange juice (改否定句)
- Friday,Saturday,Sunday,week,park这些英语单词怎么解释
- 棉花纤维可以用做造纸
- He likes dogs because they are____friendly A a kind of B kind of C a kind D kind选几哦
- 甲数除以已数,商4余3.被除数,除数,商相加的和是37.甲数······
- 我国淡水资源的总量约为多少立方米
- 求一道数学方程组的解
- It is silly _____you _____forgive others for their faults A of ,don not B for, not to C of,never to
- 概率统计问题,麻烦解释下为什么P(A-B)=P(A-AB)=P(A)-P(AB)呢?