Solved Help with column of hours and minutes summing as hh:mm (1 Viewer)

Aryzona

Member
Local time
Today, 07:41
Joined
Nov 14, 2020
Messages
49
I track the time spent on a task in hours (as number) and minutes (as number) . A person may have 7 entries in a month. I need to be able to show total hours and total minutes on the report per each employee.

Currently, i have a calculated field that takes my hours and converts them to minutes and then adds those converted minutes to the minutes field so i have maybe 910 minutes spent on that task for that person for the entire month. This should total to 15 hours and 10 Minutes. Currently my query shows 14 hours and 70 minutes and a total min of 910.

When i convert it using Mod 60 - i am getting the hours correct but the minutes are not reflected accurately.

I tried to use a function, CalcHoursOrMin that i found on the web, which works beautifully line by line but still does not give me a total per person for the entire month.

I am new to access - still learning not only the access interface but also VBA. I am pretty sure i am simply missing something.

Any help would be super!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Not sure why you're not getting the correct result. I just tried the following in the Immediate Window and got the right answer.

Code:
?910\60 & ":" & 910 mod 60
15:10
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:41
Joined
Aug 30, 2003
Messages
36,118
Something like this in a textbox:

=Int([FieldName]/60) & ":" & Format([FieldName] Mod 60,"00")
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Feb 19, 2002
Messages
42,984
Are you trying to store the data in a date data type field? Dates are points in time. They ALWAYS include a date. And they NEVER display more than 23:59 as a time. Your best bet is to convert the data to minutes when you store it initially, then you can divide by 60 to get hours and use mod to get the remainder which is minutes. I think either theDBGuy or pbaldy's solution should work for you if the data is stored as raw minutes.
 

Aryzona

Member
Local time
Today, 07:41
Joined
Nov 14, 2020
Messages
49
Hi. Welcome to AWF!

Not sure why you're not getting the correct result. I just tried the following in the Immediate Window and got the right answer.

Code:
?910\60 & ":" & 910 mod 60
15:10
I get the correct answer row by row... but i may have 10 entries for the person and when i sum the minutes to a grand total . . I can't get the report to reflect it properly .. it mods the hours ...but picks up what ever minutes are in the last row for the person rather than a total of the minutes left over after conversion. I have not tried that specific formula however (written above) so lemme give that a shot and see if it contains what i am clearly missing!

When I run the report it lists each person that did that job grouped by their name (Id#) and the line items show properly but i have not been able to get the total hours and minutes correct. Currently the report displays the correct hours as entered and the total minutes and has to be exported into excel to do the conversion of total minutes, which pretty much defeats the purpose of running the report!
 
Last edited:

Aryzona

Member
Local time
Today, 07:41
Joined
Nov 14, 2020
Messages
49
Are you trying to store the data in a date data type field? Dates are points in time. They ALWAYS include a date. And they NEVER display more than 23:59 as a time. Your best bet is to convert the data to minutes when you store it initially, then you can divide by 60 to get hours and use mod to get the remainder which is minutes. I think either theDBGuy or pbaldy's solution should work for you if the data is stored as raw minutes.
no, my fields in the table are Number not date. The entry for these are slips of paper from the person with their Name -- Job -- docs taken -- docs Processed -- Hours --- Minutes. I have the Hours field and Minutes field labeled as hours & minutes but the table field itself is a number type.
 

Aryzona

Member
Local time
Today, 07:41
Joined
Nov 14, 2020
Messages
49
Hi. Welcome to AWF!

Not sure why you're not getting the correct result. I just tried the following in the Immediate Window and got the right answer.

Code:
?910\60 & ":" & 910 mod 60
15:10
Yay ... ultimately it worked ... I had to figure out how to use the interface - but i got a grand total by name of Hours and Minutes that was accurate!!!!!! Thank you ..

I am still learning and I think there are a great deal of stuff experts take for granted that a newbie like me trips over for WEEKS... I had that general code but I didn't have the knowledge to utilize the interface properly to make it produce .. just kept getting errors... lots of study and research helped me begin to conceptualize variables and extend the concept to the report generated fields (rather than just my table fields) to get the total as a grand rather than a line by line :D

Thank you for all your help! Happy Happy Joy Joy :D
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:41
Joined
May 7, 2009
Messages
19,175
also you can use below function to convert the Time portion to Minutes:
Code:
' VBA Developer Handbook, 2nd Edition
Public Function dhCMinutes(dtmTime As Date) As Long
' Convert a date/time value to the number of
' minutes since midnight (that is, remove the date
' portion, and just work with the time part.) The
' return value can be used to calculate sums of
' elapsed time.
' Subtract off the whole portion of the date/time value
' and then convert from a fraction of a day to minutes.
dhCMinutes = TimeValue(dtmTime) * 24 * 60
End Function
you then Create a Total Query (Query1) that will Sum() all the Minutes:
Code:
select PersonID, PersonName, Sum(dhCMinutes([TaskHourSpentField])) As TotalMinutesFrom yourTable Group By PersonID, PersonName;
now that you have the Total Minutes of a person, you will then need to
convert this Total Minutes to Hours:Minute (hh:nn) format.
So you will be needing another function:
Code:
' VBA Developer Handbook, 2nd Edition
' Convert Elapsed Minutes into a Formatted String
Public Function dhCTimeStr(lngMinutes As Long) As String
' Convert from a number of minutes to a string
' that looks like a time value.
' This function is not aware of international settings.
'
dhCTimeStr = Format(lngMinutes \ 60, "0") & _
GetTimeDelimiter() & Format(lngMinutes Mod 60, "00")
End Function
so, your final Total Query will be:
Code:
select PersonID, PersonName,  dhCTimeStr([TotalMinutes) From Query1;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:41
Joined
Oct 29, 2018
Messages
21,358
Yay ... ultimately it worked ... I had to figure out how to use the interface - but i got a grand total by name of Hours and Minutes that was accurate!!!!!! Thank you ..

I am still learning and I think there are a great deal of stuff experts take for granted that a newbie like me trips over for WEEKS... I had that general code but I didn't have the knowledge to utilize the interface properly to make it produce .. just kept getting errors... lots of study and research helped me begin to conceptualize variables and extend the concept to the report generated fields (rather than just my table fields) to get the total as a grand rather than a line by line :D

Thank you for all your help! Happy Happy Joy Joy :D
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Aryzona

Member
Local time
Today, 07:41
Joined
Nov 14, 2020
Messages
49
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
ok .. one last question :D the formula is working to return the proper sum of the hours and the minutes but the formatting is an issue. Most look fine the way they are but i have one displaying as "27:5" and should display as "27:05" - as a newbie I have tried implementing the format with leading zeros but it adds the leading zero to the front so I get "027.50" - how do i get the last 2 digits "minutes" to format with a leading zero ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:41
Joined
Oct 29, 2018
Messages
21,358
ok .. one last question :D the formula is working to return the proper sum of the hours and the minutes but the formatting is an issue. Most look fine the way they are but i have one displaying as "27:5" and should display as "27:05" - as a newbie I have tried implementing the format with leading zeros but it adds the leading zero to the front so I get "027.50" - how do i get the last 2 digits "minutes" to format with a leading zero ?
Did you try the expression @pbaldy provided in post #8?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:41
Joined
Oct 29, 2018
Messages
21,358
I did not... i shall give it a whirl ! :D

That worked like a charm !!!!! Thank you so much!!!!!!
Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom