Hours, Minutes, Seconds

[MattBaldry]

Hello coding wizards,

Is anyone out there able to add seconds to the below code?

Public Function CalcHoursOrMinutes(CalcType As String, Hours, Minutes)
' This procedure calculates the values for the CalcType, "Hours" or "Minutes".
' If either Hours or Minutes is Null, the function returns Null.
On Error GoTo Err_Handler

' CalcHoursOrMinutes() Version 1.0.0
' Copyright © 2013 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact support@extramiledata.com.
' Use (at your own risk) and modify freely as long as proper credit is given.

    Dim lngTotalMinutes As Long
    Dim lngHours As Long
    Dim lngMinutes As Long

    ' Clear the values and exit if some of the calculation values are null.
    If IsNull(Hours) And IsNull(Minutes) Then
        CalcHoursOrMinutes = Null
        GoTo Exit_Proc
    End If

    ' Get the total minutes.
    lngTotalMinutes = (Nz(Hours, 0) * 60) + Nz(Minutes, 0)

    ' Get the hours.
    lngHours = Int(lngTotalMinutes / 60)

    ' Get the minutes.
    lngMinutes = lngTotalMinutes - (lngHours * 60)

    If CalcType = "Hours" Then
        CalcHoursOrMinutes = lngHours
    Else
        CalcHoursOrMinutes = lngMinutes
    End If

Exit_Proc:
    On Error Resume Next
    Exit Function

Err_Handler:
    MsgBox Err.Number & " " & Err.Description, vbInformation, _
        "CalcHoursOrMinutes()"
    CalcHoursOrMinutes = Null
    Resume Exit_Proc

https://extramiledata.com/get-total-hours-and-minutes-from-summing-columns-of-hours-and-minutes/

I have been trying all afternoon and just cannot get it to work

I am trying to get the below columns to show correctly.

Total Hours: [LabourResourceHours]*([QtyRequired]*[PieceWorkQuantity])
Total Minutes: [LabourResourceMinutes]*([QtyRequired]*[PieceWorkQuantity])
Total Seconds: [LabourResourceSeconds]*([QtyRequired]*[PieceWorkQuantity])

Like the 4,200 seconds should display as 1 hours, 10 minutes, 0 seconds

Total Hours	Total Minutes	Total Seconds
0​	0​	0​
0​	0​	0​
0​	140​	2100​
0​	0​	4200​
0​	0​	0​
0​	140​	0​
0​	210​	0​
0​	0​	4200​
0​	0​	840​
0​	0​	2100​
0​	140​	0​
0​	0​	840​
0​	0​	0​

~Matt

 

[plog]

Dim lngTotalMinutes As Long
Dim lngTotalSeconds As Long

...

lngTotalMinutes = (Nz(Hours, 0) * 60) + Nz(Minutes, 0)
lngTotalSeconds = 60*lngTotalMinutes

 

[arnelgp]

Public Function CalcHoursOrMinutesOrSeconds(ByVal CalcType As String, ByVal Hours As Variant, ByVal Minutes As Variant, ByVal Seconds As Variant)
' This procedure calculates the values for the CalcType, "Hours" or "Minutes".
' If either Hours or Minutes is Null, the function returns Null.
On Error GoTo Err_Handler

' CalcHoursOrMinutes() Version 1.0.0
' Copyright © 2013 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact support@extramiledata.com.
' Use (at your own risk) and modify freely as long as proper credit is given.
'
' modified by arnelgp
' to add "Seconds"
'
' make them static so if "the same" values for hours, minutes and seconds is passed,
' it will again do the same calculation
'
    Static lngHours As Long
    Static lngMinutes As Long
    Static lngSeconds As Long
    Static st_Seconds As Long
    Dim lngSec As Long
    Dim ret
    
    ' Clear the values and exit if some of the calculation values are null.
    If IsNull(Hours) And IsNull(Minutes) And IsNull(Seconds) Then
        CalcHoursOrMinutesOrSeconds = Null
        GoTo Exit_Proc
    End If

    ' Get the total seconds
    lngSec = (Nz(Hours, 0) * 3600) + (Nz(Minutes, 0) * 60) + Nz(Seconds, 0)
    
    If lngSec <> st_Seconds Then
        st_Seconds = lngSec
        ' get total hours
        lngHours = lngSec \ 3600
        
        ' Get the total minutes.
        lngMinutes = (lngSec - (lngHours * 3600)) \ 60
        
    
        ' get again the final seconds
        lngSeconds = lngSec - (lngHours * 3600) - (lngMinutes * 60)
    End If
    
    Select Case CalcType
        Case Is = "Hours"
            ret = lngHours
        Case Is = "Minutes"
            ret = lngMinutes
        Case Is = "Seconds"
            ret = lngSeconds
    End Select
            
    CalcHoursOrMinutesOrSeconds = ret

Exit_Proc:
    Exit Function

Err_Handler:
    MsgBox Err.Number & " " & Err.Description, vbInformation, _
        "CalcHoursOrMinutes()"
    CalcHoursOrMinutesOrSeconds = Null
    Resume Exit_Proc

End Function

 

[MarkK]

Public Function CalcHMS(CalcType As String, Optional H As Long, Optional M As Long, Optional ByVal S As Long) As Long
    S = S + M * 60 + H * 3600
    
    Select Case CalcType
        Case "H"
            CalcHMS = S / 3600
        Case "M"
            CalcHMS = S / 60
        Case "S"
            CalcHMS = S
        Case Else
            Err.Raise 5, , "Use H, M, or S as CalcType"
    End Select
End Function

 

[ebs17]

Like the 4,200 seconds should display as 1 hours, 10 minutes, 0 seconds

Below 24 hours, you have times wonderfully under control.

? DateAdd("s", 4200, 0), TimeSerial(0, 0, 4200), TimeSerial(0, 140, 2100), TimeSerial(0, 140, 0)
01:10:00      01:10:00      02:55:00      02:20:00