The VBA code should calculate intervals greater than 24 hours between 2 dates at different times

DailyKode
Mesaje:6
Membru din:Vin Feb 17, 2023 10:12 pm
The VBA code should calculate intervals greater than 24 hours between 2 dates at different times

Mesaj de DailyKode » Dum Mar 31, 2024 1:38 pm

Greetings community!
I am trying to write the following VBA code but something is not working.
Take a look please, maybe I'm missing something and help me with a solution.

The VBA code should calculate intervals greater than 24 hours between 2 dates at different times (dd.mm.yyyy hh:mm), located in different columns on the spreadsheet, and return a value in the HOME1 sheet on the column H of the form hh:mm or hh:mm:ss. Likewise in the sheets HOME2 and HOME3 on cells columns I and J respectively.

Thank you in advance!

============================================================================================


Public Function Durate(dday As Double) As String
Dim s As String
Dim minute As Long
Dim hour As Long
Dim RestMinute As Long

If dday < 0 Then
s = "_"
minute = dday * 24 * 60 * (-1)
Else
s = ""
minute = dday * 24 * 60
End If

RestMinute = minute Mod 60
hour = (minute - RestMinute) / 60

If hour < 10 Then s = s & "0"
s = s & hour & ":"
If RestMinute < 10 Then s = s & "0"
s = s & RestMinute

Durate = s
End Function
Sub CalculDurate()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long

' For HOME1
Set ws = ThisWorkbook.Sheets("HOME1")
lastRow = ws.Cells(ws.Rows.Count, 8).End(xlUp).Row
For i = 2 To lastRow
ws.Cells(i, 8).Value = Durate(ws.Cells(i, 8).Value)
Next i

' For HOME2
Set ws = ThisWorkbook.Sheets("HOME2")
lastRow = ws.Cells(ws.Rows.Count, 6).End(xlUp).Row
For i = 2 To lastRow
ws.Cells(i, 6).Value = Durate(ws.Cells(i, 6).Value)
Next i

' For HOME3
Set ws = ThisWorkbook.Sheets("HOME3")
lastRow = ws.Cells(ws.Rows.Count, 12).End(xlUp).Row
For i = 2 To lastRow
ws.Cells(i, 12).Value = Durate(ws.Cells(i, 12).Value)
Next i
End Sub

Înapoi la “Visual Basic for Application (VBA) cu Excel - Intrebari tehnice”