如何判断小于1小时，还是大于1小时。

ning84 · 发表于 2024-8-24 22:52

本帖最后由 ning84 于 2024-8-24 22:53 编辑

Sub LLL()
Dim Rng As Range, oRng As Range
Dim Sht As Worksheet
Dim ii, jj
Set Sht = Sheet2
Set Rng = Sht.Cells(10, 1).CurrentRegion
Debug.Print Rng.Address
With Sht
.Cells.Font.Size = 9
For ii = 1 To Rng.Rows.Count
Set oRng = Rng(ii, 1)
.Cells(ii + 4, "C") = "= text(" & .Cells(oRng.Row + oRng.Rows.Count - 1, 1).Address & "-" & .Cells(Rng.Row, 1).Address & ", " & """hh小时:mm分:ss秒"")"
.Cells(ii + 4, "E") = "=" & """" & .Name & ii & "，用时""" & " & text(" & .Cells(oRng.Row + oRng.Rows.Count - 1, 1).Address & "-" & .Cells(Rng.Row, 1).Address & ", " & """hh小时:mm分:ss秒"")"
Next ii
End With
End Sub

复制代码

小于1小时--- = TEXT($A$5-$A$5, "、mm分:ss秒")
大于1小时--- = TEXT($A$5-$A$5, "hh小时:mm分:)

ning84 · 发表于 2024-8-25 04:10

If Format(t, "h:m:s") < #1:00:00 AM# Then

Sub LLL()
Dim Rng As Range, oRng As Range
Dim Sht As Worksheet
Dim ii, jj
Set Sht = Sheet2
Set Rng = Sht.Cells(10, 1).CurrentRegion
Debug.Print Rng.Address
With Sht
.Cells.Font.Size = 9
For ii = 1 To Rng.Rows.Count
Set oRng = Rng(ii, 1)
t = .Cells(oRng.Row + oRng.Rows.Count - 1, 1) - .Cells(Rng.Row, 1)
Debug.Print Format(t, "h:m:s"), Format(t, "h:m:s") < #1:00:00 AM#
If Format(t, "h:m:s") < #1:00:00 AM# Then
.Cells(ii + 4, "C") = "= text(" & .Cells(oRng.Row + oRng.Rows.Count - 1, 1).Address & "-" & .Cells(Rng.Row, 1).Address & ", " & """m分:s秒"")"
.Cells(ii + 4, "E") = "=" & """" & .Name & ii & "，用时""" & " & text(" & .Cells(oRng.Row + oRng.Rows.Count - 1, 1).Address & "-" & .Cells(Rng.Row, 1).Address & ", " & """m分:s秒"")"
Else
.Cells(ii + 4, "C") = "= text(" & .Cells(oRng.Row + oRng.Rows.Count - 1, 1).Address & "-" & .Cells(Rng.Row, 1).Address & ", " & """h小时:m分"")"
.Cells(ii + 4, "E") = "=" & """" & .Name & ii & "，用时""" & " & text(" & .Cells(oRng.Row + oRng.Rows.Count - 1, 1).Address & "-" & .Cells(Rng.Row, 1).Address & ", " & """h小时:m分"")"
End If
Next ii
End With
End Sub

复制代码

ning84 · 发表于 2024-8-30 19:54

学习笔记

Private Sub delde()
Dim Rng As Range
Dim Sht As Worksheet
Set Rng = Selection.CurrentRegion
Set Sht = Rng.Parent
Debug.Print Rng.Address
Set Rng = Sht.Cells(Rng.Row, 1).Resize(Rng.Rows.Count, 1)
Debug.Print Rng.Address
For ii = 1 To Rng.Rows.Count
Debug.Print Rng(, 1).Address, Rng(ii, 1).Address
HhMmSs Rng(, 1), Rng(ii, 1)
Next ii
End Sub
Function HhMmSs(sDateRng As Range, oDateRng As Range)
Dim Sht As Worksheet
Set Sht = sDateRng.Parent
Dim Tt As Date, tDate As Date
tDate = #1:00:00 AM#
With Sht
Tt = oDateRng - sDateRng
If Tt < tDate Then
.Cells(oDateRng.Row, "D") = "= text(" & oDateRng.Address & "-" & sDateRng.Address & ", " & """m分s秒"")"
Else
.Cells(oDateRng.Row, "D") = "= text(" & oDateRng.Address & "-" & sDateRng.Address & ", " & """h小时m分"")"
End If
End With
End Function

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 如何判断小于1小时 ，还是大于1小时。