怎么用vba代码把单元格数字提取出来变成日期?
答案:3 悬赏:0 手机版
解决时间 2021-12-31 20:52
- 提问者网友:战魂
- 2021-12-31 10:51
原格式是这样的:20161001 11:05:21 ,想要把日期和时间分别提取到不同列,日期变成这样的格式:2016/10/01,时间保持:11:05:21
最佳答案
- 五星知识达人网友:杯酒困英雄
- 2021-12-31 10:59
请查收附件。代码如下。
sub tq()
dim r, n as long
dim rg as range
dim bgarr
r = range("a" & rows.count).end(xlup).row
bgarr = range("b1:g" & r)
n = mid(range("a" & r), 2, 1)
for each rg in range("a1:a" & r)
bgarr(rg.row, 1) = --left(rg, 1)
bgarr(rg.row, 2) = --mid(rg, 2, 1)
bgarr(rg.row, 3) = --right(rg, 1)
bgarr(rg.row, 4) = --right(bgarr(rg.row, 1) + n, 1)
bgarr(rg.row, 5) = --right(bgarr(rg.row, 2) + n, 1)
bgarr(rg.row, 6) = --right(bgarr(rg.row, 3) + n, 1)
next
range("b:g").clearcontents
range("b1:g" & r) = bgarr
end sub
sub tq()
dim r, n as long
dim rg as range
dim bgarr
r = range("a" & rows.count).end(xlup).row
bgarr = range("b1:g" & r)
n = mid(range("a" & r), 2, 1)
for each rg in range("a1:a" & r)
bgarr(rg.row, 1) = --left(rg, 1)
bgarr(rg.row, 2) = --mid(rg, 2, 1)
bgarr(rg.row, 3) = --right(rg, 1)
bgarr(rg.row, 4) = --right(bgarr(rg.row, 1) + n, 1)
bgarr(rg.row, 5) = --right(bgarr(rg.row, 2) + n, 1)
bgarr(rg.row, 6) = --right(bgarr(rg.row, 3) + n, 1)
next
range("b:g").clearcontents
range("b1:g" & r) = bgarr
end sub
全部回答
- 1楼网友:夜余生
- 2021-12-31 12:36
Range("B1") = Format(Range("A1"), "yyyy-mm-dd")
Range("B2") = Format(Range("A1") + 1, "yyyy-mm-dd")
- 2楼网友:千夜
- 2021-12-31 11:25
Public Function ToDate(str As String)
Dim st
st = Trim(str)
dat = Format(Left(st, 4) + "/" + Mid(st, 3, 2) + "/" + Mid(st, 5, 2) + " " + Right(st, Len(st) - InStr(st, " ")), "yyyy/MM/dd hh:mm;ss")
End Function
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
点此我要举报以上问答信息
大家都在看
推荐资讯