gpt4 book ai didi

excel - 如何拆分日期和时间

转载 作者:行者123 更新时间:2023-12-04 21:11:54 33 4
gpt4 key购买 nike

我想知道如何将日期和时间分成单独的行。

enter image description here

我想要 A 列中的日期和 B 列中的时间,包括 AM/PM。

我尝试使用空格分隔符,但出现错误。在互联网上,人们首先选择单元格,但我想在不选择单元格的情况下这样做。

Sub CompareTime()

Dim ws As Worksheet

Dim lastRow As Long
Dim arr As Long
Dim test As Double

Set ws = ActiveSheet

Cells(1, 2).EntireColumn.Insert

'Find last data point
With ws
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For Count = 1 To lastRow
'split date
Cells(1, Count).Offset(0, 1) = Split(Cells(1, Count).Value, " ")
Next Count

End Sub

最佳答案

日期/时间是一个数字而不是文本字符串,因此要获取您需要从小数中拆分整数的日期,然后对其进行格式化:

Sub CompareTime()

Dim ws As Worksheet

Dim lastRow As Long
Dim Count As Long
Dim test As Double

Set ws = ActiveSheet


'Find last data point
With ws
.Columns(2).Insert
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For Count = 5 To lastRow
'split date
test = .Cells(Count, 1).Value2
.Cells(Count, 1).Value2 = Int(test)
.Cells(Count, 1).NumberFormat = "m/d/yyyy"
.Cells(Count, 1).Offset(0, 1).Value2 = test - Int(test)
.Cells(Count, 1).Offset(0, 1).NumberFormat = "hh:mm AM/PM"

Next Count
End With

关于excel - 如何拆分日期和时间,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47999466/

33 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com