Hours and Minutes

For HanDBase related topics that are not specific to one platform.

Hours and Minutes

Postby royaltiger » Thu Jul 26, 2018 8:31 am

Hi all
I'm a bit stuck in trying to add up some records with hours and minutes, so I know what I've worked in a week, I have basically created a new database that houses 7 days of records into one new record, creating a week of work, using the database popup to catch the info from previous database, although the initial records are in time format, my problem of course starts because i have retrieved the info through the Database popup, which obviously keeps the format HH:MM in the cell, but the cell no longer recognises the format as time, and is unable to calculate properly, is there a proper formula or a workaround that someone can suggest so my 7 days of hours and minutes worked, add up as a time of hours and minutes. Hopefully it all makes sense
royaltiger
 
Posts: 20
Joined: Wed Mar 16, 2016 6:29 am

Re: Hours and Minutes

Postby aa6vhDave » Thu Jul 26, 2018 9:40 am

HanDBase internally stores time as an integer (number of minutes since midnight). So its quite easy to perform math on the time.

However, when you use calculated fields, and display the field as time (HH:MM), the time is converted from that integer count into a HH:MM string format. Which makes that calculated field a little useless for further time calculations.

So what I do is specify the intermediate calculated fields to be displayed as an integer. Only in the last field that will be shown to the user is the time calculation field displayed as HH:MM.
aa6vhDave
 
Posts: 56
Joined: Mon Aug 19, 2013 1:42 pm

Re: Hours and Minutes

Postby royaltiger » Fri Jul 27, 2018 7:11 am

So I think I've worked out the workaround for this, I've had to add a calculated field to my hours worked database , added the field of hours worked "F10", multiply by 24 and multiply by 60, and change result in properties to integer, so 12 hours 17 minutes "Hours Worked" turns into 63676800, Example((F10*24)*60)
I now use database popup field to grab the calculated integer value field, In my new database, i had to again create a new calculated field "Total Value" which adds the integer fields together, keep properties as integer, I then had to create yet another calculated field "Total Hours"(F39), which is the "Total Value" field then divide by 24 and divide by 60, change properties to time, Example ((F39/24)/60). Whether i was doing something wrong i'm not sure, but if i Totaled the integer values together and then divided by 24 then 60, the calculation came out wrong, Example F1+F2+F3 /24/60 gave me a wrong time format but "totalvalue" F39/24/60 gave me a correct answer. Hopefully this makes sense and may help some others
royaltiger
 
Posts: 20
Joined: Wed Mar 16, 2016 6:29 am

Re: Hours and Minutes

Postby mjhanna » Fri Jul 27, 2018 11:30 am

By now you have probably figured out that
"Example F1+F2+F3 /24/60 gave me a wrong time"
will of course give you the wrong time. The divisions only affect F3. You need to use parens around the sum:
(F1+F2+F3) /24/60

But as you found out, dividing a separate field with holds the sum also works.
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 160
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: Hours and Minutes

Postby royaltiger » Sun Jul 29, 2018 1:40 pm

Sometimes the best thing about posting is how more experienced users can see what you've done wrong so quickly, which makes learning that bit easier.
royaltiger
 
Posts: 20
Joined: Wed Mar 16, 2016 6:29 am


Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 0 guests