(Solved) – Time differnce to hh/mm/ss converion-VBA

(solved)-–-time-differnce-to-hh/mm/ss-converion-vba

I have a code that will convert the difference in time in any given day to HH MM SS.

Sub timefind()

Dim eDate As Date

Dim StartDate As Date

Dim mHours As Long, mMinutes As Long, mSeconds As Double

'Dim iValue As Variant

Dim wb1 As Workbook

Dim ws1 As Worksheet

Dim ws2 As Worksheet

Dim ws1lastrow As Double

Dim tymval As Double

Set wb1 = Workbooks("Time_Comparison.xlsm")

Set ws1 = wb1.Worksheets("Input")

Set ws2 = wb1.Worksheets("Dashboard")

Range("A1").EntireRow.Insert

ws1.Cells(1, 1).Value = "Date"
ws1.Cells(1, 2).Value = "Time"
ws1.Cells(1, 3).Value = "Hours"
ws1.Cells(1, 4).Value = "Minutes"
ws1.Cells(1, 5).Value = "Seconds"

ws1lastrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

StartDate = ws1.Cells(2, 2).Value

For tymval = 2 To ws1lastrow

    Set curCell = ws1.Cells(tymval, 1)

    Set nextCell = ws1.Cells(tymval, 1).Offset(1, 0)

    If nextCell.Value <> curCell.Value Then

    eDate = ws1.Cells(tymval, 1).Offset(0, 1)
    'to hours
    ws1.Cells(tymval, 3) = DateDiff("s", StartDate, eDate) / (60 * 60)
    ws1.Cells(tymval, 3) = Int(ws1.Cells(tymval, 3))

    'to Minutes
    ws1.Cells(tymval, 4) = DateDiff("s", StartDate, eDate) / 60 - ws1.Cells(tymval, 3) * 60
    ws1.Cells(tymval, 4) = Int(ws1.Cells(tymval, 4))

    'to seconds
    ws1.Cells(tymval, 5) = DateDiff("s", StartDate, eDate) / 60 - ws1.Cells(tymval, 3) * 60 - ws1.Cells(tymval, 4)
    ws1.Cells(tymval, 5) = ws1.Cells(tymval, 5) * 60

    StartDate = ws1.Cells(tymval, 1).Offset(1, 1)

End If

Next

End Sub

Now the new requirement is, if the minutes is <=30 the minutes should be considered as 30 minutes and if the minutes is >30 it should be considered as 60 minutes and added to the hour.
How to do it.

Leave a Reply

Your email address will not be published. Required fields are marked *