Calculate hours by just entering number of hours/minutes without a start end time

[eobrien]

Good Morning,

I want to just put in actual hours worked by site. So, if 5 and a half hours worked at one location 5.30 would be put in. If 5 hours and 15 minutes were worked somewhere 5.15 would be put under that site. I have tried different formatting to give me a sum/result that will compute to regular hours/minutes, but doesn't work. Actually, results don't give enough hours/minutes worked. For example at one site where 2 hours and 90 minutes + 5 hours and 10 minutes is resulting in 8 hours versus 8 hours and 40 minutes. I get it, that it's just putting minutes into whole numbers. I am sure this is an easy fix with formatting, but not coming up with it. Any assistance is appreciated. Thank you. E

 

[SeniorNewbie]

Hi eobrien!
It's not exactly what you were asking for, but this code contains your solution (and much more)! Just look for the comment converts hours2dec

Option Explicit

Sub ActivityStart(sJob As String)
System
Dim r As Integer, vTime
With wsT

r = .Cells(.Rows.Count, 2).End(xlUp).Row + 1              'first empty row in activity
.Columns(5).Interior.Color = xlNone                      'reset highlight
vTime = Round(Time * 24 / (1 / 4), 0) * (1 / 4) / 24    'rounds current time to 15 mins

    .Cells(r, 2) = sJob                      'as sent by double click
    .Cells(r, 3) = Date                      'system date
    .Cells(r, 4) = vTime                     'as rounded above
    .Cells(r, 5).Interior.Color = 14348258   'highlight
 
.Cells(r, 5).Activate                        'set target

End With
End Sub

Sub ActivityEnd(r As Integer)
System
Dim vTime, dDuration As Date, sDuration As String
With wsT

    If .Cells(r, 5) = "" Then
        vTime = Round(Time * 24 / (1 / 4), 0) * (1 / 4) / 24                'rounds current time to 15 mins
        .Cells(r, 5) = vTime
    End If
 
    dDuration = .Cells(r, 5) - .Cells(r, 4)                                   'calculates the needed time
    sDuration = Int(CSng(dDuration * 24)) & ":" & Format(dDuration, "nn")   'formatting the time value to hours:mins
    .Cells(r, 6) = sDuration
 
    .Columns(5).Interior.Color = xlNone                      'reset highlight

End With
End Sub

Sub ActivitySummary()
System
Dim f As Byte, i As Integer, r As Integer
Dim sMsg As String, sName As String, sFile As String
Dim dStart As Date, dEnd As Date, iDays As Integer
Dim Hours As Double, sHours As String
Dim Expenses As Double, sExpenses As String
Dim aDesc, aVal, rng As Range
With wsT

r = .Cells(.Rows.Count, 2).End(xlUp).Row
sFile = Left(wb.Name, Len(wb.Name) - 5) 'cuts the extension

dStart = wsf.Min(.Columns(3))            'first entry
dEnd = wsf.Max(.Columns(3))              'last entry
iDays = DateDiff("d", dStart, dEnd) + 1

Hours = wsf.Sum(.Columns(6)) * 24        'converts hours2dec
sHours = Format(Hours, "0.00")

Expenses = wsf.Sum(.Columns(7))          'just a sum
sExpenses = Format(Expenses, "0.00")

sName = Application.UserName            'who's playing around here?

                                        'mergin and formatting all info to a string / msgbox
sMsg = "Summary from " & dStart & " to " & dEnd & " = " & iDays & " days." & vbNewLine & vbNewLine & vbTab
sMsg = sMsg & "In total there were " & sHours & " hours of work" & vbNewLine & vbTab
sMsg = sMsg & "and " & sExpenses & " EUR of expenses." & vbNewLine

sMsg = sMsg & vbNewLine & vbTab & "Do you need a hardcopy?"

f = MsgBox(sMsg, 259, "ActivitySummary for " & sName & " | " & sFile)   'forked to save paper for unnecessary hardcopies
Select Case f
    Case 6
        GoTo PRN
    Case Else
        Exit Sub
End Select

Sorry! I have to explain: in a standard module I define wsf in a first step as WorkSheetFunction and then as in the Sub System: Set wsf = Worksheetfunction. Avoids a lot of typing on the job
wsT = ThisWorkBook.Sheets("Activities")
Have fun!

Newbie sr.

 

[eobrien]

You have overestimated my super powers. I was looking for an excel formatting/formula fix. I appreciate the feedback, but I don't think I have the VBA coding tool in my "tool box" aka brain bandwidth! E.

 

[Fluff]

How about

=DOLLARFR(SUM(DOLLARDE(+C2:M2,60)),60)

 

[SeniorNewbie]

Sorry E.
Sometimes I get so fixated on my VBA hobby that I don't understand why XL users struggle with formulas. But actually, I think your task would have a good chance of being solved with a user-defined function. Let me have a look. I'll get back to you.

 

[SeniorNewbie]

Here you go:

Function time2dec(Val)
Dim hours
    hours = Val * 24
    time2dec = Format(hours, "0.00")
End Function

Put this code to a normal module and use it easily with other formulas

Have fun!
Newbie sr.

 

[eobrien]

How about

=DOLLARFR(SUM(DOLLARDE(+C2:M2,60)),60)

If I type just two numbers and add those up it works, but if I lay into my spreadsheet it gives me a #VALUE. My format is all "Number"

 

[Fluff]

Do those cells contain a formula that returns ""

 

[Dave Patton]

Please post an example of your data with the forum's tool named XL2BB.

Is your requirement just to calculate the totals?

Consider the following:

Time 2023.xlsm
	C	D
17	3.00
18	20.15
19	40.50
20	64.08	hours
2g

Cell Formulas
Range	Formula
C20	C20	=SUM(INT(C17:C19),MOD(C17:C19,1)*100/60)

 

[eobrien]

Please post an example of your data with the forum's tool named XL2BB.

Is your requirement just to calculate the totals?

Consider the following:

Time 2023.xlsm
	C	D
17	3.00
18	20.15
19	40.50
20	64.08	hours
2g

Cell Formulas
Range	Formula
C20	C20	=SUM(INT(C17:C19),MOD(C17:C19,1)*100/60)

That does compute a more accurate answer than straight math, but still off. I can't download the forum tool. Says "this view file isn't supported in protective view". I can't get around it. May be my network security..